Subquery with Aggregate - 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)

Subquery with Aggregate

Author

Topic

thiyait
Yak Posting Veteran

70 Posts

Posted - 2014-04-29 : 07:46:34

HI Everyone,

I trying to get below result

CC total po TOA
101 5O 36
201 100 45

using below query but getting error: how do i achieve above result

SELECT PR.DIMENSION2_ as CC,count(*) as 'total po'
(
SELECT COUNT(DISTINCT PA.purchId)
FROM PRUCHAGREE PA
INNER JOIN PRUCHAGREELINE PREQL
ON PA.PURCHRFQCASEID = PRFQCT.RFQCASEID AND PA.DATAAREAID = PRFQCT.DATAAREAID
WHERE PREQL.PURCHID = PR.purchid AND PREQL.DATAAREAID = PR.DATAAREAID
) as 'TOA'
FROM PURCHTABLE PR
WHERE PR.DATAAREAID = @DataAreaId and (CONVERT(DATE,PR.CREATEDDATETIME,112) >= @FromDate)
GROUP BY PR.DIMENSION2_

Here is error message
Column PURCHTABLE .purchid' is invalid in the select list because...
Column PURCHTABLE .DATAAREAID' is invalid in the select list because...

Thanks and regards,
thiya.

Tusharp86
Starting Member

9 Posts

Posted - 2014-04-29 : 08:00:06

Can you please provide the DML AND DDL structure so we can analysis
and one more thing after " count(*) as 'total po' " ,(comma) is missing

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-04-29 : 11:37:34

You can't use a correlated sub query like that when going an aggregate. Here is a link that might help explain:
http://www.sqlteam.com/article/aggregating-correlated-sub-queries

Also, it is generaly considered bad form to use a single quote for a quoted identifier, as that is the SQL Server string delimiter. Rather you should use double-quotes (") or, even better, square-brackets ([]) or the best is to not make identifiers that need to be quoted.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources