not contained in either an aggregate function or..

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

General SQL Server Forums

New to SQL Server Programming

not contained in either an aggregate function or..

Author

Topic

jobejufranz
Starting Member

33 Posts

Posted - 2008-11-21 : 04:53:25

Hi guys,

I just wanted to ask for help to analyze my query. I'm getting an error

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

in my query.

Here is my query

SELECT 
	FLOOR(CONVERT(INT,iDate-'2001-01-01')/7) AS 'Week',
	(CASE WHEN iBranch='NORTHWEST' THEN (SUM(iTotal)*1.0)/(COUNT(DISTINCT(iCode))*1.0) END) AS 'NW', 	
	((SUM(iTotal)*1.0)/(COUNT(DISTINCT(iCode))*1.0)) AS 'TOTAL' 	
FROM 
	tblInvoice 
WHERE 
	(iDate>='7/28/2008' AND iDate<'11/17/2008') AND 
	iCode LIKE '%-%' 
GROUP BY 
	FLOOR(CONVERT(INT,iDate-'2001-01-01')/7)
ORDER BY 
	FLOOR(CONVERT(INT,iDate-'2001-01-01')/7) DESC;

I tried but i guess i can't find the error. If I take out this line

quote:
(CASE WHEN iBranch='NORTHWEST' THEN (SUM(iTotal)*1.0)/(COUNT(DISTINCT(iCode))*1.0) END) AS 'NW',

it is working. Unfortunately i need to aggregate it by iBranch.

I gave up thinking, my brain is already stuck.

Any help will be highly appreciated. TIA

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-21 : 05:02:31

Posted - 11/21/2008 : 04:53:25
--------------------------------------------------------------------------------

SELECT 
	FLOOR(CONVERT(INT,iDate-'2001-01-01')/7) AS 'Week',
	SUM(CASE WHEN iBranch='NORTHWEST' THEN iTotal ELSE NULL END) *1.0/COUNT(DISTINCT iCode) AS 'NW', 	
	SUM(iTotal)*1.0/COUNT(DISTINCT iCode) AS 'TOTAL' 	
FROM 
	tblInvoice 
WHERE 
	(iDate>='7/28/2008' AND iDate<'11/17/2008') AND 
	iCode LIKE '%-%' 
GROUP BY 
	FLOOR(CONVERT(INT,iDate-'2001-01-01')/7)
ORDER BY 
	FLOOR(CONVERT(INT,iDate-'2001-01-01')/7) DESC;

seems like what you need is above

jobejufranz
Starting Member

33 Posts

Posted - 2008-11-24 : 03:17:48

quote:

Originally posted by visakh16

Posted - 11/21/2008 : 04:53:25
--------------------------------------------------------------------------------

SELECT 
	FLOOR(CONVERT(INT,iDate-'2001-01-01')/7) AS 'Week',
	SUM(CASE WHEN iBranch='NORTHWEST' THEN iTotal ELSE NULL END) *1.0/COUNT(DISTINCT iCode) AS 'NW', 	
	SUM(iTotal)*1.0/COUNT(DISTINCT iCode) AS 'TOTAL' 	
FROM 
	tblInvoice 
WHERE 
	(iDate>='7/28/2008' AND iDate<'11/17/2008') AND 
	iCode LIKE '%-%' 
GROUP BY 
	FLOOR(CONVERT(INT,iDate-'2001-01-01')/7)
ORDER BY 
	FLOOR(CONVERT(INT,iDate-'2001-01-01')/7) DESC;

seems like what you need is above

Exactly what i needed! Though i need to modify a bit but it works perfectly. Below is what I exactly need.


SUM(CASE WHEN iBranch='NORTHWEST' THEN iTotal ELSE NULL END) *1.0/COUNT(DISTINCT(CASE WHEN iBranch='NORTHWEST' THEN iCode ELSE NULL END) AS 'NW'

Thanks a lot.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-24 : 03:21:07

cheers

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-24 : 04:24:35

Easier week caclulation

SELECT		DATEDIFF(DAY, '20010101', GETDATE()) / 7 AS [Week],
		SUM(CASE WHEN iBranch = 'NORTHWEST' THEN 1.0E * iTotal ELSE 0.0E END) / COUNT(DISTINCT CASE WHEN CASE WHEN iBranch = 'NORTHWEST' THEN iCode ELSE NULL END) AS [NW],
		1.0E * SUM(iTotal) / (COUNT(DISTINCT iCode) AS [TOTAL]
FROM		tblInvoice 
WHERE		iDate >= '20080728'
		AND iDate < '20081117'
		AND iCode LIKE '%-%'
GROUP BY	DATEDIFF(DAY, '20010101', GETDATE()) / 7
ORDER BY	DATEDIFF(DAY, '20010101', GETDATE()) / 7 DESC

E 12°55'05.63"
N 56°04'39.26"

jobejufranz
Starting Member

33 Posts

Posted - 2008-11-24 : 05:09:34

quote:

Originally posted by Peso

Easier week caclulation

SELECT		DATEDIFF(DAY, '20010101', GETDATE()) / 7 AS [Week],
		SUM(CASE WHEN iBranch = 'NORTHWEST' THEN 1.0E * iTotal ELSE 0.0E END) / COUNT(DISTINCT CASE WHEN CASE WHEN iBranch = 'NORTHWEST' THEN iCode ELSE NULL END) AS [NW],
		1.0E * SUM(iTotal) / (COUNT(DISTINCT iCode) AS [TOTAL]
FROM		tblInvoice 
WHERE		iDate >= '20080728'
		AND iDate < '20081117'
		AND iCode LIKE '%-%'
GROUP BY	DATEDIFF(DAY, '20010101', GETDATE()) / 7
ORDER BY	DATEDIFF(DAY, '20010101', GETDATE()) / 7 DESC

E 12°55'05.63"
N 56°04'39.26"

Thanks Peso will try.

Guys got another problem.


COUNT(CASE WHEN iBranch='NORTHWEST' THEN iCode END)*1.0/COUNT(DISTINCT(CASE WHEN iBranch='NORTHWEST' THEN iCode END))*1.0 AS 'NW'

I'm getting the error below

quote:

Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.
Warning: Null value is eliminated by an aggregate or other SET operation.

Since this is a dynamic query, can i do something if the value is NULL?

Thanks again guys.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-24 : 05:14:03

try


COUNT(CASE WHEN iBranch='NORTHWEST' THEN iCode END)*1.0/NULLIF(COUNT(DISTINCT(CASE WHEN iBranch='NORTHWEST' THEN iCode END)),0) AS 'NW'

jobejufranz
Starting Member

33 Posts

Posted - 2008-11-24 : 21:03:21

Thanks a lot guys for the reply.

To visakh16:

It works perfectly for me and again I have to add on both side, NULLIF on numerator and NULLIF on denominator.

I understand that using functions to check NULL values always had a great impact on overall performance of the query. Can it be minimized? I cannot do something to eliminate NULL values because this is by design, I just do data mine but not the developer.

To Peso:

Does this query of yours had a significant increase on performance over my query?

This query


DATEDIFF(DAY, '20010101', GETDATE()) / 7 AS [Week]

over this query


FLOOR(CONVERT(INT,GETDATE()-'2001-01-01')/7) AS 'Week'

What are the advantages because i've already used my query in several pages of my project? Your explanation is highly appreciated.

Again, thank you very much guys for the reply.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-25 : 01:17:16

DATEDIFF / 7 is two operations.
- CONVERT / FLOOR is four operations.

E 12°55'05.63"
N 56°04'39.26"

jobejufranz
Starting Member

33 Posts

Posted - 2008-11-25 : 03:45:17

quote:
Originally posted by Peso

DATEDIFF / 7 is two operations.
- CONVERT / FLOOR is four operations.

Yeah, your right. It will really make a lot of difference, especially mining large amount of data.

I'm changing my query now. Thanks a lot mate.

Cheers.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources