Trouble with Grouping - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Trouble with Grouping

Author

Topic

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2007-04-10 : 08:35:54

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)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-10 : 08:43:05

) as d GROUP BY accountno HAVING COUNT (*) >2)

Peter Larsson
Helsingborg, Sweden

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2007-04-10 : 08:56:24

Am i missing something?

I just tried what you suggested (see below)
_______________________________________-

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'
) as d GROUP BY accountno HAVING COUNT (*) >2)
______________________________________________
An got "Incorrect syntax near the keyword 'as'"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-10 : 09:05:09

You don't state wether or not duplicates should be found within a table or across tables

select		c1.accountno
from		contact1 AS c1
inner join	(
			SELECT	accountno
			FROM	contsupp 
			WHERE	rectype = 'p'
				AND CAST(SUBSTRING(City, 9, 8) AS DATETIME) BETWEEN '20060101' AND '20070430'
				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'
		) AS d on d.accountno = c1.accountno
GROUP		accountno
HAVING		COUNT (*) > 2

Peter Larsson
Helsingborg, Sweden

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2007-04-10 : 09:08:58

I am not very experienced at writing TQSL queries so am a little unsure as to what you mean.
But if an accountno appears in either of the two queries I want to include it(thats' why I used union all)

Is it possible to use Group By on the result of 2 union queries?

I am not sure whether it is?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-10 : 09:11:58

Yes, it is.
I think this will get you better query plan.

SELECT	d.AccountNo
FROM	(
		SELECT		AccountNo
		FROM		Contact1
		GROUP BY	AccountNo
		HAVING		COUNT (*) > 2
	) AS d
WHERE	EXISTS (
			SELECT	*
			FROM	ContSupp AS cs
			WHERE	RecType = 'p'
				AND CAST(SUBSTRING(City, 9, 8) AS DATETIME) BETWEEN '20060101' AND '20070430'
				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%'
					)
				AND cs.AccountNo = d.AccountNo
		)
	OR EXISTS	(
				SELECT	*
				FROM	GMSM_GMBASE.dbo.cal AS c
				WHERE	rectype = 'S'
					AND c.AccountNo = d.AccountNo
			)

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-10 : 09:13:22

If this doesn't help you, please post some sample data for the three tables and your expected output.
OR explain more in detail what you are trying to accomplish.

Peter Larsson
Helsingborg, Sweden

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2007-04-10 : 09:43:29

Thanks for your help Peso.
I will try to be clearer.
Let me think for a few mins then I'l post what I am trying to achieve.

:O)

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2007-04-10 : 10:16:57

Contact1 Table This is my contacts table (names, addresses etc)

Accountno | FirstName | LastName
X123 | Mike | Bell
Y234 | Steven | Hunt
Z456 | Gemma | Smith
A123 | Mary | Martin

ContSupp Table This table show what events they attended and when.
I know the column headings are silly but that’s the way they are.

Accountno | Contact | rectype | City
Z456 | Training | P | FRED 2007011012:15pm
A123 | Focus Event | P | PETER 2001030112:13pm
A123 | Committee Meet| P | LUCY 2001030110:12am
Z456 | Site Audit | p | FRED 2002020109:13am
Y234 | Training | P | FRED 2003060103:13pm
Y234 | Training | P | SALLY 2007030812:13am
Y234 | Other | P | JIM 2002030812:13am

Cal Table This is pending activities table (rectype of ‘s’ means a pending sale)

Accountno | rectype | amount
M786 | S | 123.00
N567 | S | 546.00
Z456 | S | 5467.00
D301 | S | 6541.00
X123 | S | 4509.00

I want to show a list of all the people who meet a target of 3. (these represent my busiest customers)
Any of the relevant entries in Contsupp = 1 point

A sale in the Cal table also represents 1 point.

So in the example above only Gemma Smith gets 3 points as she has 2 points from the Contsupp table and 1 point from the cal table.

Steven Hunt (Y234) has three entries in Contsupp but only 2 of them are included in my select list.

Hope this makes sense.

Here is my query that isn’t currently working;

select accountno, firstname, lastname 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)
-----------------------------------------------------------------------------------------

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-10 : 10:27:07

[code]SELECT d.AccountNo,
c1.FirstName,
c1.LastName
FROM (
SELECT AccountNo
FROM ContSupp
WHERE RecType = 'p'
AND CAST(SUBSTRING(City, 9, 8) AS DATETIME) BETWEEN '20060101' AND '20070430'
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'
) AS d
INNER JOIN Contact1 AS c1 ON c1.AccountNo = d.AccountNo
GROUP BY d.AccountNo,
c1.FirstName,
c1.LastName
HAVING COUNT(*) > 2[/code]

Peter Larsson
Helsingborg, Sweden

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2007-04-10 : 10:34:06

Hooray...Thank you sir!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-10 : 10:35:50

See how fast you got an accurate response when you give us more information to work with?

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources