Help! Display 1 row showing total records with GROUP BY

Reply

mbernardo Posted: 05-18-2006 8:54 AM

rated by 0 users

Help! Consider the following table:

Sub_ID   Aff_ID   Entry_date   Clicks
-------------------------------------
33       232      5/1/2006     1
42       422      5/2/2006     1
33       83       5/2/2006     3
76       333      5/3/2006     1
... and so on

I have the following statement in SQL Query Analyzer:

SELECT count(*) as counter
FROM Ad_Stats_Table 
WHERE Aff_ID = 33 AND (Entry_Date between '5/1/2006' and  '5/16/2006') 
Group By Sub_ID 

That query returns 35,830 rows. But it returns the following:

counter
-------
1
1
1
1
... (35,830 times!)

I want it to return just 1 row containing the row count like so:

counter
-------
35830

Can someone help?

Reply

keithrull replied on 05-18-2006 12:52 PM

rated by 0 users

Try This:

[code language="T-SQL"]

select count(*)
from Ad_Stats_Table
Where
Sub_ID = 33
And
( entry_date
 between '5/1/2006' and  '5/16/2006')
Group By Sub_ID

[/code]

or you can also group it by aff_id

[code language="T-SQL"]

[/code]

select count(*)
from Ad_Stats_Table
Where
Aff_ID = 333
And
( entry_date
 between '5/1/2006' and  '5/16/2006')
Group By Aff_ID