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 :-)

Problem with select every TOP 1 records from different group in a

Hi Expert,
I came across a situation where the query result returns all non-NULL
records even I use TOP 1 statement. Here is the SQL statement:
SELECT CONTRACT_NUMBER, STEP, STATUS_END_DATE, CIS_PK
FROM dbo.[VIEW1] T1
WHERE (CONTRACT_NUMBER = 'S07-123A' OR
CONTRACT_NUMBER = 'S07-127A' OR
CONTRACT_NUMBER = 'S07-129A') AND (STEP =
(SELECT TOP 1 STEP
FROM dbo.[VIEW2] T2
WHERE T2.CIS_PK = T1.CIS_PK AND
T2.STATUS_END_DATE IS NOT NULL
ORDER BY STEP ASC))
For the above SQL statement, "CIS_PK is key". "STEP" is unique within each
CONTRACT_NUMBER group.
The incorrect result that I got is:
CONTRACT_NUMBER STEP DATE
CIS_PK
S05-137A 4 8/11/2006
728
S05-137A 7 10/1/2006
731
I am expecting the right answer, which is:
S05-137A 4 8/11/2006
728
Thanks,
-adam
I just figured it out by myself. The where clause comparison
T2.CIS_PK=T1.CIS_PK should be T2.CONTRACT_NUMBER = T1.CONTRACT_NUMBER.
Thanks anyway.
-adam
"adam" wrote:

> Hi Expert,
> I came across a situation where the query result returns all non-NULL
> records even I use TOP 1 statement. Here is the SQL statement:
> SELECT CONTRACT_NUMBER, STEP, STATUS_END_DATE, CIS_PK
> FROM dbo.[VIEW1] T1
> WHERE (CONTRACT_NUMBER = 'S07-123A' OR
> CONTRACT_NUMBER = 'S07-127A' OR
> CONTRACT_NUMBER = 'S07-129A') AND (STEP =
> (SELECT TOP 1 STEP
> FROM dbo.[VIEW2] T2
> WHERE T2.CIS_PK = T1.CIS_PK AND
> T2.STATUS_END_DATE IS NOT NULL
> ORDER BY STEP ASC))
> For the above SQL statement, "CIS_PK is key". "STEP" is unique within each
> CONTRACT_NUMBER group.
> The incorrect result that I got is:
> CONTRACT_NUMBER STEP DATE
> CIS_PK
> S05-137A 4 8/11/2006
> 728
> S05-137A 7 10/1/2006
> 731
> I am expecting the right answer, which is:
> S05-137A 4 8/11/2006
> 728
> Thanks,
> -adam

Problem with rs:SessionId

shotHi group,
When I open a report in my IE a new session (ej:zxzxzxzxzx...) was insert
into table SessionData in my ReportServerTempDB. It's OK. My report hava same
groups and has many drill-drops
But when I open other IE and I give this URL:
http://myServer/reportserver?my_report&rs:SessionId=zxzxzxzx...&rs:Format=PDF,
my server insert a new session and didn't see the same snapshot that the
first time
Any one know why?
Thanks
--
Sorry for my English :)The New IE window is a new session. You cannot share one session with
another.
--
| Thread-Topic: Problem with rs:SessionId
| thread-index: AcTn/YJPkfXUSSGMT06ypAjdL1VwIw==| X-WBNR-Posting-Host: 194.224.254.81
| From: "=?Utf-8?B?SnVhbiBDYXJsb3M=?=" <jcrf@.discussions.microsoft.com>
| Subject: Problem with rs:SessionId
| Date: Wed, 22 Dec 2004 00:09:07 -0800
| Lines: 18
| Message-ID: <EE150D62-13F7-4A0B-A325-AC655FEE5722@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
| Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.reportingsvcs:37925
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| shotHi group,
|
| When I open a report in my IE a new session (ej:zxzxzxzxzx...) was insert
| into table SessionData in my ReportServerTempDB. It's OK. My report hava
same
| groups and has many drill-drops
|
| But when I open other IE and I give this URL:
|
http://myServer/reportserver?my_report&rs:SessionId=zxzxzxzx...&rs:Format=PD
F,
| my server insert a new session and didn't see the same snapshot that the
| first time
|
|
| Any one know why?
|
| Thanks
|
| --
| Sorry for my English :)
||||ok, thanks, but how it does button "export"?, because when I export to pdf, a
new window it open and the pdf file have my session options.
""Brad Syputa - MS"" wrote:
> The New IE window is a new session. You cannot share one session with
> another.
> --
> | Thread-Topic: Problem with rs:SessionId
> | thread-index: AcTn/YJPkfXUSSGMT06ypAjdL1VwIw==> | X-WBNR-Posting-Host: 194.224.254.81
> | From: "=?Utf-8?B?SnVhbiBDYXJsb3M=?=" <jcrf@.discussions.microsoft.com>
> | Subject: Problem with rs:SessionId
> | Date: Wed, 22 Dec 2004 00:09:07 -0800
> | Lines: 18
> | Message-ID: <EE150D62-13F7-4A0B-A325-AC655FEE5722@.microsoft.com>
> | MIME-Version: 1.0
> | Content-Type: text/plain;
> | charset="Utf-8"
> | Content-Transfer-Encoding: 7bit
> | X-Newsreader: Microsoft CDO for Windows 2000
> | Content-Class: urn:content-classes:message
> | Importance: normal
> | Priority: normal
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> | Newsgroups: microsoft.public.sqlserver.reportingsvcs
> | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
> | Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl
> | Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.reportingsvcs:37925
> | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
> |
> | shotHi group,
> |
> | When I open a report in my IE a new session (ej:zxzxzxzxzx...) was insert
> | into table SessionData in my ReportServerTempDB. It's OK. My report hava
> same
> | groups and has many drill-drops
> |
> | But when I open other IE and I give this URL:
> |
> http://myServer/reportserver?my_report&rs:SessionId=zxzxzxzx...&rs:Format=PD
> F,
> | my server insert a new session and didn't see the same snapshot that the
> | first time
> |
> |
> | Any one know why?
> |
> | Thanks
> |
> | --
> | Sorry for my English :)
> |
>|||I think he was mistaken. A linked window is part of the same session.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Juan Carlos" <jcrf@.discussions.microsoft.com> wrote in message
news:433D9268-CA7B-46C5-8E26-D8914801CC04@.microsoft.com...
> ok, thanks, but how it does button "export"?, because when I export to
> pdf, a
> new window it open and the pdf file have my session options.
>
> ""Brad Syputa - MS"" wrote:
>> The New IE window is a new session. You cannot share one session with
>> another.
>> --
>> | Thread-Topic: Problem with rs:SessionId
>> | thread-index: AcTn/YJPkfXUSSGMT06ypAjdL1VwIw==>> | X-WBNR-Posting-Host: 194.224.254.81
>> | From: "=?Utf-8?B?SnVhbiBDYXJsb3M=?=" <jcrf@.discussions.microsoft.com>
>> | Subject: Problem with rs:SessionId
>> | Date: Wed, 22 Dec 2004 00:09:07 -0800
>> | Lines: 18
>> | Message-ID: <EE150D62-13F7-4A0B-A325-AC655FEE5722@.microsoft.com>
>> | MIME-Version: 1.0
>> | Content-Type: text/plain;
>> | charset="Utf-8"
>> | Content-Transfer-Encoding: 7bit
>> | X-Newsreader: Microsoft CDO for Windows 2000
>> | Content-Class: urn:content-classes:message
>> | Importance: normal
>> | Priority: normal
>> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>> | Newsgroups: microsoft.public.sqlserver.reportingsvcs
>> | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
>> | Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl
>> | Xref: cpmsftngxa10.phx.gbl
>> microsoft.public.sqlserver.reportingsvcs:37925
>> | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
>> |
>> | shotHi group,
>> |
>> | When I open a report in my IE a new session (ej:zxzxzxzxzx...) was
>> insert
>> | into table SessionData in my ReportServerTempDB. It's OK. My report
>> hava
>> same
>> | groups and has many drill-drops
>> |
>> | But when I open other IE and I give this URL:
>> |
>> http://myServer/reportserver?my_report&rs:SessionId=zxzxzxzx...&rs:Format=PD
>> F,
>> | my server insert a new session and didn't see the same snapshot that
>> the
>> | first time
>> |
>> |
>> | Any one know why?
>> |
>> | Thanks
>> |
>> | --
>> | Sorry for my English :)
>> |
>>

problem with query

Hello group,
I have the following issue:
select convert(varchar, GETDATE(), 105)
--
30-10-2007
(1 row(s) affected)
select convert(varchar, ini_fecha_actividad, 105) from agenda
--
28-10-2007
30-10-2007
31-10-2007
01-11-2007
(4 row(s) affected)
select convert(varchar, ini_fecha_actividad, 105) from agenda where
convert(varchar,ini_fecha_actividad,105) between convert(varchar,
GETDATE(), 105) AND convert(varchar, dateadd(day,1,GETDATE()), 105)
--
30-10-2007
31-10-2007
(2 row(s) affected)
select convert(varchar, ini_fecha_actividad, 105) from agenda where
convert(varchar,ini_fecha_actividad,105) between convert(varchar,
GETDATE(), 105) and convert(varchar, dateadd(day,5,GETDATE()), 105)
--
(0 row(s) affected)
What is the problem with my last query ? It should return:
30-10-2007
31-10-2007
01-11-2007
Any help will be REALLY appreciated.
greetings,
hansHi Hans,
The problem is that in your WHERE clause you are comparing the dates as
character strings. As such the end period in your BETWEEN becomes
'05-11-2007'. That sorts alphabetically before any of the other dates and
your range does not return any results.
One of your queries returns the desired results but that is just by
coincidence (because in that case the character representation of the end
date is higher in order).
The correct way to return a range of dates is to compare dates in their
native format, not to convert them. To trim the time portion you can use
different techniques, one is via using the DATEADD and DATEDIFF functions.
Here is a query that will return the desired results:
SELECT CONVERT(VARCHAR, ini_fecha_actividad, 105)
FROM Agenda
WHERE ini_fecha_actividad >= DATEADD(day, DATEDIFF(day, '20010101',
CURRENT_TIMESTAMP), '20010101')
AND ini_fecha_actividad < DATEADD(day, DATEDIFF(day, '20010101',
CURRENT_TIMESTAMP) + 6, '20010101');
Note that I changed the math and added 6 days to the upper boundary to
include the results dates for the next 5 days.
HTH,
Plamen Ratchev
http://www.SQLStudio.com|||Hans, you should try this approach which compares dates with datetypes.
declare @.d1 smalldatetime
set @.d1 = '11-01-2007' -- Nov 1st
select convert(varchar, @.d1, 105)
where @.d1 between GETDATE() and dateadd(day,5,GETDATE())
answer: 01-11-2007
I removed the references to a table but you get the format - works the same.
--
James Chacata
jameschac@.hotmail.com
"Hans" wrote:
> Hello group,
> I have the following issue:
> select convert(varchar, GETDATE(), 105)
> --
> 30-10-2007
> (1 row(s) affected)
>
>
> select convert(varchar, ini_fecha_actividad, 105) from agenda
> --
> 28-10-2007
> 30-10-2007
> 31-10-2007
> 01-11-2007
> (4 row(s) affected)
>
>
> select convert(varchar, ini_fecha_actividad, 105) from agenda where
> convert(varchar,ini_fecha_actividad,105) between convert(varchar,
> GETDATE(), 105) AND convert(varchar, dateadd(day,1,GETDATE()), 105)
> --
> 30-10-2007
> 31-10-2007
> (2 row(s) affected)
>
>
> select convert(varchar, ini_fecha_actividad, 105) from agenda where
> convert(varchar,ini_fecha_actividad,105) between convert(varchar,
> GETDATE(), 105) and convert(varchar, dateadd(day,5,GETDATE()), 105)
> --
> (0 row(s) affected)
>
> What is the problem with my last query ? It should return:
> 30-10-2007
> 31-10-2007
> 01-11-2007
>
> Any help will be REALLY appreciated.
> greetings,
> hans
>