Problem with SELECT, GROUP BY and aggregate function

Hi all,

I have a problem with an SQL-query and I don't know what the best solution would be to solve the problem.

/*INSERT INTO WERKS (
WERKS.Z8601,
WERKS.Z8602,
WERKS.Z8603,
WERKS.Z8604,
WERKS.Z8605,
WERKS.Z8606,
WERKS.Z8607,
WERKS.Z8608,
WERKS.Z8609,
WERKS.Z8610,
WERKS.Z8611,
WERKS.Z8621,
WERKS.Z8622,
WERKS.Z8623,
WERKS.Z8624,
WERKS.Z8625,
WERKS.Z8626,
WERKS.Z8627,
WERKS.Z8628,
WERKS.Z8629,
WERKS.Z8630,
WERKS.Z8631,
WERKS.Z8632) */
SELECT
0,
Stati.z4414,
Stati.z4402,
'',
'',
'',
Isnull((select Srtas.z02 from Srtas where Srtas.z00 = Stati.z4400 and Srtas.z01 = Stati.z4414), ''),
Isnull((select Klant.z0102 From Klant where Klant.z0101 = Stati.z4402), ''),
'',
'',
'',
sum (Case when Stati.z4407 = 200609 then Stati.z4409 Else 0 End),
sum (Case when Stati.z4407 = 200609 then Stati.z4410 Else 0 End),
sum (Case when Stati.z4407 = 200509 then Stati.z4409 Else 0 End),
sum (Case when Stati.z4407 = 200509 then Stati.z4410 Else 0 End),
sum (Case when Stati.z4407 Between 200510 and 200609 then Stati.z4409 Else 0 End),
sum (Case when Stati.z4407 Between 200510 and 200609 then Stati.z4410 Else 0 End),
sum (Case when Stati.z4407 Between 200410 and 200509 then Stati.z4409 Else 0 End),
sum (Case when Stati.z4407 Between 200410 and 200509 then Stati.z4410 Else 0 End),
sum (Case when Stati.z4407 = 200609 then Stati.z4411 Else 0 End),
sum (Case when Stati.z4407 = 200509 then Stati.z4411 Else 0 End),
sum (Case when Stati.z4407 Between 200510 and 200609 then Stati.z4411 Else 0 End),
sum (Case when Stati.z4407 Between 200410 and 200509 then Stati.z4411 Else 0 End)
FROM STATI
WHERE
(Stati.z4402 Between '40000' AND 'ZONE6') AND
(Stati.z4414 Between '2005028' AND '2005028') AND
(Stati.z4417 = 'A')
GROUP BY Stati.z4414, Stati.z4402

I get the following error:

Msg 8120, Level 16, State 1, Line 25
Column 'STATI.Z4400' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

I know it has something todo with the select on the table SRTAS, but what's the best way to solve this problem without the chance of getting a wrong result.

The SELECT on SRTAS is to get the "description" of STATI.Z4414 who's stored in the table SRTAS.
I only want to group on the fields that will be inserted in WERKS.Z8602, WERKS.Z8603, WERKS.Z8604, WERKS.Z8605, WERKS.Z8606. So adding STATI.Z4400 to the GROUP BY would give me wrong results?

This query is dynamicly generated from within my program from what the user selected.

Also, if there are better ways to write the query, I would be happy to get some hints and tips, but if possible without stored procedures.

Thanks in advance!

If you really want to use that dynamic query, I would use a Subquery to use the column names to group, otherwise it often the case that you use the overview over the statement.

SELECT col1,col2
FROM
(
Your dynamic query here
) Subquery
Group by col1, col2

HTH, Jens K. Suessmeyer.

http://www.sqlserver205.de|||Assuming the value of STAI.Z4400 is always the same for the group do this:
.
.
.
MAX(Isnull((select Srtas.z02 from Srtas where Srtas.z00 = Stati.z4400 and Srtas.z01 = Stati.z4414), '')),
MAX(Isnull((select Klant.z0102 From Klant where Klant.z0101 = Stati.z4402), '')),
.
.
.

That should fix it.

In this case, it would be easier to JOIN the STAI file intead of doing the subquery. Then just do

CASE WHEN Srtas.z02 IS NULL THEN '' ELSE Srtas.z02 END,
CASE WHEN Klant.z0102 IS NULL THEN '' ELSE Klant.z0102 END,

|||Thank you both for your opinion and help on this problem.

Reading your posts gave me some new ideas so I started changing the query like this:

SELECT

0,

Stati.z4414,

Stati.z4402,

'',

'',

'',

Max(Srtas.z02),

Max(Klant.z0102),

'',

'',

'',

sum (Case when Stati.z4407 = 200609 then Stati.z4409 Else 0 End),

sum (Case when Stati.z4407 = 200609 then Stati.z4410 Else 0 End),

sum (Case when Stati.z4407 = 200509 then Stati.z4409 Else 0 End),

sum (Case when Stati.z4407 = 200509 then Stati.z4410 Else 0 End),

sum (Case when Stati.z4407 Between 200510 and 200609 then Stati.z4409 Else 0 End),

sum (Case when Stati.z4407 Between 200510 and 200609 then Stati.z4410 Else 0 End),

sum (Case when Stati.z4407 Between 200410 and 200509 then Stati.z4409 Else 0 End),

sum (Case when Stati.z4407 Between 200410 and 200509 then Stati.z4410 Else 0 End),

sum (Case when Stati.z4407 = 200609 then Stati.z4411 Else 0 End),

sum (Case when Stati.z4407 = 200509 then Stati.z4411 Else 0 End),

sum (Case when Stati.z4407 Between 200510 and 200609 then Stati.z4411 Else 0 End),

sum (Case when Stati.z4407 Between 200410 and 200509 then Stati.z4411 Else 0 End)

FROM STATI

LEFT JOIN KLANT ON STATI.Z4400 = KLANT.Z0100 AND STATI.Z4402 = KLANT.Z0101

LEFT JOIN SRTAS ON STATI.Z4400 = SRTAS.Z00 AND STATI.Z4414 = SRTAS.Z01

WHERE

(Stati.z4402 Between '40000' AND 'ZONE6') AND

(Stati.z4414 Between '2005028' AND '2005028') AND

(Stati.z4417 = 'A')

GROUP BY Stati.z4414, Stati.z4402

This query seems to work.

I have to admit that there's a huge pile of dust ontop of my MSSQL-knowledge. Been a while since I last used it.

So if any of you have any comments on me doing something wrong, I would be pleased to hear it :-)