aggregate function query help - 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 2008 Forums

Transact-SQL (2008)

aggregate function query help

Author

Topic

Ratz03
Starting Member

27 Posts

Posted - 2014-12-30 : 16:26:27

Hi All,

I am running the query below and it gives me error - A.MAICDT' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

I need all these columns in my query. Please help.

SELECT C.MAPORF, C.MAMAPC
,DATEPART(YYYY, CONVERT (DATETIME, CONVERT(VARCHAR(8),C.MAICDT + 19000000))), ''
,CONVERT (DATETIME, CONVERT(VARCHAR(8),C.MAICDT + 19000000))
,'xyz'
,SUM(CASE WHEN A.E3AMCD = 'COM' THEN A.E3E3A1 ELSE 0 END)
,SUM(CASE WHEN A.E3AMCD = 'IPT' THEN A.E3E3A1 ELSE 0 END)
,SUM(CASE WHEN A.E3AMCD = 'PRM' THEN A.E3E3A1 ELSE 0 END)
FROM A
Join B
ON A.E3E0CD = B.E2E0CD and A.E3E2CD = B.E2E2CD
JOIN C
ON B.E2MANU = C.MAMANU AND B.E2MASE = C.MAMASE
group by C.MAPORF

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-12-30 : 16:45:39

You'll need to use a CTE to get that value. Here's an example:

with cte (col1, col2)
as (select col1, sum(col2) as col2 from table1 group by col1)
select table1.col1, cte.col2, table1.col3
from table1
join cte on table1.col1 = cte.col1

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-12-30 : 16:51:43

Edit: I'm assuming MAPORF can't be NULL. If it can, some adjustments need made to the code.


SELECT C.MAPORF, C.MAMAPC 
	,DATEPART(YYYY, CONVERT (DATETIME, CONVERT(VARCHAR(8),C.MAICDT + 19000000))), '' 
    ,CONVERT (DATETIME, CONVERT(VARCHAR(8),C.MAICDT + 19000000)) 
    ,A_Totals.COM
    ,A_Totals.IPT
    ,A_Totals.PRM
	,'xyz'
	FROM A
	Join (
	    SELECT MAPORF
    	    ,SUM(CASE WHEN A.E3AMCD = 'COM' THEN A.E3E3A1 ELSE 0 END) AS COM 
	        ,SUM(CASE WHEN A.E3AMCD = 'IPT' THEN A.E3E3A1 ELSE 0 END) AS IPT
    	    ,SUM(CASE WHEN A.E3AMCD = 'PRM' THEN A.E3E3A1 ELSE 0 END) AS PRM    
	    FROM A
	    GROUP BY MAPORF
	) AS A_Totals ON A_Totals.MAPORF = A.MAPORF
	Join B
	ON A.E3E0CD = B.E2E0CD and A.E3E2CD = B.E2E2CD
	JOIN C
	ON B.E2MANU = C.MAMANU AND B.E2MASE = C.MAMASE
GROUP BY C.MAPORF

Ratz03
Starting Member

27 Posts

Posted - 2015-01-02 : 08:06:51

Thanks ScottPletcher for the response, but it is not working as MAPORF is not a valid column for table A. MAPORF column exists only on table C.

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2015-01-05 : 11:18:44

Sorry, good point!


SELECT C.MAPORF, C.MAMAPC 
	,DATEPART(YYYY, CONVERT (DATETIME, CONVERT(VARCHAR(8),C.MAICDT + 19000000))), '' 
    ,CONVERT (DATETIME, CONVERT(VARCHAR(8),C.MAICDT + 19000000)) 
    ,A_Totals.COM
    ,A_Totals.IPT
    ,A_Totals.PRM
	,'xyz'
	FROM A
	Inner Join (
	    SELECT E3E0CD, E3E2CD
    	    ,SUM(CASE WHEN A.E3AMCD = 'COM' THEN A.E3E3A1 ELSE 0 END) AS COM 
	        ,SUM(CASE WHEN A.E3AMCD = 'IPT' THEN A.E3E3A1 ELSE 0 END) AS IPT
    	    ,SUM(CASE WHEN A.E3AMCD = 'PRM' THEN A.E3E3A1 ELSE 0 END) AS PRM    
	    FROM A
	    GROUP BY E3E0CD, E3E2CD
	) AS A_Totals ON A_Totals.E3E0CD = A.E3E0CD AND A_Totals.E3E2CD = A.E3E2CD
	Inner Join B
	ON A.E3E0CD = B.E2E0CD and A.E3E2CD = B.E2E2CD
	Inner Join C
	ON B.E2MANU = C.MAMANU AND B.E2MASE = C.MAMASE
GROUP BY C.MAPORF

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources