basic query question

Hello,
I was wondering how best to write a valid query that
Assimilates the following query.
SELECT id,COUNT(*) AS cnt FROM table_name WHERE cnt > 0
GROUP BY id;
basically it's a query that returns a recordset with
unique values for one of the alias fields
any suggestions greatly appreciatedYou can't use the column alias in the where clause, but if I understand your
query correctly, you don't need it.
SQL Server will only returns rows for id's which actually occur, so their
count will always be greater than 0.
Just use hte query you have without the WHERE clause.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"aylwin" <aylwinagena@.hotmail.com> wrote in message
news:00ed01c3939d$db056be0$a001280a@.phx.gbl...
> Hello,
> I was wondering how best to write a valid query that
> Assimilates the following query.
> SELECT id,COUNT(*) AS cnt FROM table_name WHERE cnt > 0
> GROUP BY id;
> basically it's a query that returns a recordset with
> unique values for one of the alias fields
> any suggestions greatly appreciated|||Hi thank you,
My actual query is different. my example is simplified.
My actual query is more like:
SELECT z.application_id as AppID, queued_by_user, queued_date,
'-1' AS NoInQueue, ProcessType, Reprint_Flag, First_Name
FROM l_DraftPrintingQueue z, l_customer a, d_customer_detail b
WHERE suspendprinting = 0
and
a.customer_id = b.customer_id
and
b.application_id = z.application_id
--Group By AppID <<problem>>
ORDER BY first_name ASC
the query returns many AppID's. I was trying to use a Group By but there
seems to be a problem with the where clause.
any ideas' thanks again!!!
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||See my replies in the other group you posted to. Please don't multipost.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"aylwin agena" <aylwinagena@.hotmail.com> wrote in message
news:%23PWmhh6kDHA.2964@.tk2msftngp13.phx.gbl...
> Hi thank you,
> My actual query is different. my example is simplified.
> My actual query is more like:
> SELECT z.application_id as AppID, queued_by_user, queued_date,
> '-1' AS NoInQueue, ProcessType, Reprint_Flag, First_Name
> FROM l_DraftPrintingQueue z, l_customer a, d_customer_detail b
> WHERE suspendprinting = 0
> and
> a.customer_id = b.customer_id
> and
> b.application_id = z.application_id
> --Group By AppID <<problem>>
> ORDER BY first_name ASC
> the query returns many AppID's. I was trying to use a Group By but there
> seems to be a problem with the where clause.
> any ideas' thanks again!!!
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!|||select id, count(*) as cnt from table_name
group by id having count(*) > 0|||The HAVING clause is unnecessary here as you won't ever ever count an id
value if it doesn't appear in the data, so the counts will always be
positive.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"bill" <anonymous@.discussions.microsoft.com> wrote in message
news:2DBC44C3-5418-4B4C-8B4F-CD18F62A9761@.microsoft.com...
> select id, count(*) as cnt from table_name
> group by id having count(*) > 0