SQL Query Help anyone??

[icw]

Hi I am having problems using the Group by.
My queory uses a union between 2 subqueries and I am trying to pull out those that appear more than 2 times can anyone help?
I get a "Incorrect syntax near the keyword 'GROUP'" error when i try to run the query in Query Analyzer.
Can you help at all
------------------------
HERES THE QUERY;

select accountno from contact1 where accountno in

((SELECT accountno FROM contsupp
WHERE rectype = 'p' AND CAST(SUBSTRING(city,9,8) AS datetime)between '01/01/2006' and '04/30/2007'
AND
(contact LIKE '%Focus%'
or contact LIKE '%Training%'
or contact LIKE '%Course%'
or contact LIKE '%HDI%'
or contact LIKE '%Executive Forum%'
or contact LIKE '%Benchmark buddy%'
or contact LIKE '%Committee%'
or contact LIKE '%Consultancy%'
or contact LIKE '%Helpline%'
or contact LIKE '%Leadership%'
or contact LIKE '%Site Aud%'
or contact LIKE '%Site Ass%'
or contact LIKE '%Site Re%')
UNION ALL
SELECT accountno FROM GMSM_GMBASE.dbo.cal WHERE rectype = 'S'
)GROUP BY accountno HAVING COUNT (*) >2)

 

[Cowboy Bob]

Try "GROUP BY accountno HAVING COUNT(accountno) > 2"

 

[icw]

Unfortunately it results i the same message!

 

[Cowboy Bob]

What DB? Just tested on one of my tables in DB2 and it doesn't work with the outer select. Does this work?

SELECT accountno FROM contsupp
WHERE rectype = 'p' AND CAST(SUBSTRING(city,9,8) AS datetime)between '01/01/2006' and '04/30/2007'
AND
(contact LIKE '%Focus%'
or contact LIKE '%Training%'
or contact LIKE '%Course%'
or contact LIKE '%HDI%'
or contact LIKE '%Executive Forum%'
or contact LIKE '%Benchmark buddy%'
or contact LIKE '%Committee%'
or contact LIKE '%Consultancy%'
or contact LIKE '%Helpline%'
or contact LIKE '%Leadership%'
or contact LIKE '%Site Aud%'
or contact LIKE '%Site Ass%'
or contact LIKE '%Site Re%')
UNION ALL
SELECT accountno FROM GMSM_GMBASE.dbo.cal WHERE rectype = 'S'
GROUP BY accountno HAVING COUNT (accountno) > 2;

 

[Weltchy]

Add a tablename just after you define your unioned tables, along the lines of

or contact LIKE '%Site Ass%'
or contact LIKE '%Site Re%')
UNION ALL
SELECT accountno FROM GMSM_GMBASE.dbo.cal WHERE rectype = 'S'
) Table1 GROUP BY accountno HAVING COUNT (*) >2)

Oh, and as matey above said, use COUNT(AccountNo) not COUNT(*)

Also make sure you have your indexes setup correctly, else using Like will slow the execution down somewhat!!!!

And finally, change your date format to YYYYMMDD. Using DD/MM/YYYY is an easy way to make mistakes

 

[MarillionFan]

Amateurs.

 

[Spacecadet]

Looks like goldmine data which is a ****tard to work with on the best of days

 

[Weltchy]

Amateurs.

Know-it-all!!!