Cannot perform an aggregate function on an express

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)

Cannot perform an aggregate function on an express

Author

Topic

Pete_N
Posting Yak Master

181 Posts

Posted - 2011-01-05 : 12:27:06

I am trying to get a count of transactions per month from the transDB. The date the files was submitted is in the headsDB. I have tried the following query but get the subquery error. I would normally have all the months in there but have stripped it doen for this post

Select YEAR(SubmittedDate) as [YEAR],
SUM(CASE WHEN MONTH(SubmittedDate) = '01' Then (Select COUNT(*) from transDB where transDB.LedgerKey = headsDB.LedgerKey AND MONTH(headsDB.SubmittedDate) = '01') ELSE 0 END ) AS 'JAN'
FROM headsDB

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-01-05 : 12:32:49

Select YEAR(headsDB.SubmittedDate) as [YEAR],
SUM(CASE WHEN MONTH(headsDB.SubmittedDate) = '01') THEN 1 ELSE 0 END) AS 'JAN'
FROM headsDB
join transDB
ON transDB.LedgerKey = headsDB.LedgerKey
GROUP BY YEAR(headsDB.SubmittedDate)

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Pete_N
Posting Yak Master

181 Posts

Posted - 2011-01-05 : 12:54:59

Thanks for that, it works a teat
:)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-01-06 : 05:31:15

Also

Select YEAR(headsDB.SubmittedDate) as [YEAR],
COUNT(*) AS JAN
FROM headsDB
join transDB
ON transDB.LedgerKey = headsDB.LedgerKey
WHERE MONTH(headsDB.SubmittedDate) = 1
GROUP BY YEAR(headsDB.SubmittedDate)

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources