count of a group by having - 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 2005 Forums

Transact-SQL (2005)

count of a group by having

Author

Topic

cindy_l
Starting Member

6 Posts

Posted - 2010-02-17 : 07:13:31

I trying to get a count of the rows returned by this code.

SELECT WORK_SYS_NAME, PROJ_CODE, SUM(CASE TRANS_TYPE WHEN 'AC' THEN amt1 ELSE 0 END) AS ACTUALS,
SUM(CASE TRANS_TYPE WHEN 'BC' THEN amt1 ELSE 0 END) AS BUDGET
FROM F1WRK_PROJ_CTL_PBAL
WHERE (TRANS_TYPE IN ('AC', 'BC')) AND (PERIOD BETWEEN 1 AND 12) AND (YEAR_NAME = 2010)
GROUP BY WORK_SYS_NAME, PROJ_CODE
HAVING (SUM(CASE TRANS_TYPE WHEN 'AC' THEN amt1 ELSE 0 END) > SUM(CASE TRANS_TYPE WHEN 'BC' THEN amt1 ELSE 0 END))

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-17 : 07:17:03

but what you're actually returning is sum. probabaly what you want is this



SELECT WORK_SYS_NAME, PROJ_CODE, SUM(CASE TRANS_TYPE WHEN 'AC' THEN 1 ELSE 0 END) AS ACTUALS, 
SUM(CASE TRANS_TYPE WHEN 'BC' THEN 1 ELSE 0 END) AS BUDGET
FROM F1WRK_PROJ_CTL_PBAL
WHERE (TRANS_TYPE IN ('AC', 'BC')) AND (PERIOD BETWEEN 1 AND 12) AND (YEAR_NAME = 2010)
GROUP BY WORK_SYS_NAME, PROJ_CODE
HAVING (SUM(CASE TRANS_TYPE WHEN 'AC' THEN 1 ELSE 0 END) > SUM(CASE TRANS_TYPE WHEN 'BC' THEN 1 ELSE 0 END))

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

cindy_l
Starting Member

6 Posts

Posted - 2010-02-17 : 07:23:32

sorry i may not have been clear in my explanation. The original query returns the correct number of rows (120). I just want the count of this i.e 120

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-02-17 : 07:31:09

Probably this?


SELECT COUNT(*)
FROM
(

SELECT WORK_SYS_NAME, PROJ_CODE, SUM(CASE TRANS_TYPE WHEN 'AC' THEN amt1 ELSE 0 END) AS ACTUALS, 
SUM(CASE TRANS_TYPE WHEN 'BC' THEN amt1 ELSE 0 END) AS BUDGET
FROM F1WRK_PROJ_CTL_PBAL
WHERE (TRANS_TYPE IN ('AC', 'BC')) AND (PERIOD BETWEEN 1 AND 12) AND (YEAR_NAME = 2010)
GROUP BY WORK_SYS_NAME, PROJ_CODE
HAVING (SUM(CASE TRANS_TYPE WHEN 'AC' THEN amt1 ELSE 0 END) > SUM(CASE TRANS_TYPE WHEN 'BC' THEN amt1 ELSE 0 END))

)
Results

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

cindy_l
Starting Member

6 Posts

Posted - 2010-02-17 : 07:44:57

thanks Charlie. perfect!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources