Select/Group by/Having

[williamkremer]

Hi all -- I'm trying to get a query which will return ONLY results which have a count greater than a certain number (in this case, 200, but that's arbitrary) but my query is returning odd results so I don't trust it.
***************************************
select count(*) as No_imports, createon as Date_created, Createby from contact1 where createon in
(select createon from contact1 group by createon having count(*) > 200)
group by createon, Createby
order by createon, Createby

*******RETURNS:
No_imports Date_created Createby
----------- ---------------------- --------
4 2004-04-02 00:00:00.000 IVANNIA
8 2004-04-02 00:00:00.000 MICHELLE
400 2004-04-02 00:00:00.000 MUDIT
1 2004-04-02 00:00:00.000 RACHAEL
8 2004-04-02 00:00:00.000 TRINA
3 2004-04-12 00:00:00.000 EMEE
17 2004-04-12 00:00:00.000 IVANNIA
200 2004-04-12 00:00:00.000 LISAL
6 2004-04-12 00:00:00.000 MICHELLE
1 2004-04-12 00:00:00.000 TRINA
9 2004-05-25 00:00:00.000 EMEE
5 2004-05-25 00:00:00.000 IVANNIA
382 2004-05-25 00:00:00.000 LISAL
4 2004-05-25 00:00:00.000 MICHELLE
****************************
I am getting 4, 8, 1, 8, etc, as well as the numbers over 200. What am I doing wrong (this time)?
Thanks for helping.

 

[gmmastros]

Does this give you what you want?

select count(*) as No_imports, 
       createon as Date_created, 
       Createby 
from   contact1 
group by createon, Createby 
having count(*) > 200
order by createon, Createby

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[williamkremer]

It does indeed, George! Thank you for your quick and accurate reply!

 

[SQLBill]

The reason your script didn't work is you only dealt with CREATE_ON.

You selected the CREATE_ON for rows with no_imports greater than 200. So your sub-select got this:

Create_On
2004-04-02 00:00:00.000

Because, this was greater than 200...
400 2004-04-02 00:00:00.000 MUDIT

Then your Select looked for all rows WHERE create_on equaled that create_on value and got this:

4 2004-04-02 00:00:00.000 IVANNIA
8 2004-04-02 00:00:00.000 MICHELLE
400 2004-04-02 00:00:00.000 MUDIT
1 2004-04-02 00:00:00.000 RACHAEL
8 2004-04-02 00:00:00.000 TRINA

Because ALL of those rows have the create_on date which matches your WHERE clause. Only the Subselect 'cared' about the no_imports value.

George's script worked because he made the whole script 'care' about the no_imports requirement.

Got it?

-SQLBill


Posting advice: FAQ481-4875

 

[williamkremer]

Ah - HA!