Need assistance with totals - 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)

Need assistance with totals

Author

Topic

cjg
Starting Member

4 Posts

Posted - 2010-03-19 : 14:32:14

I am using the following script to set two columns (pre_trial and post_trial) based on values in a couple of other columns. This is the script.

SELECT jmmain.agency,
archrg.book_id,
case when[Bondstatus]='ACTI' Or [BondStatus]='BKRL' Or [BondStatus]='OR' Or [Bondstatus]='PR' then 1 end AS Pre_Trial ,
case when[Bondstatus]='SENT' Or [BondStatus]='DISM' then 1 end AS Post_Trial
FROM ARCHRG INNER JOIN jmmain ON ARCHRG.book_id = jmmain.book_id
WHERE (((jmmain.bkstatus)='A') AND ((ARCHRG.chrgdesc)<>'OUT OF COUNTY') AND ((ARCHRG.fel_misd)='F'))
order by jmmain.agency, archrg.book_id

I need use this logic and gather a grand total that counts each unique agency / book_id combination that has pre_trial > 0 and also a total for post_trial >0. This is what the data from this script looks like.
agency book_id Pre_trial Post_Trial
BCCC 262586 NULL NULL
BCCC 262586 NULL 1
BCCC 265079 NULL NULL
BCCC 280749 1 NULL
BCCC 280749 1 NULL
BCCC 280853 NULL 1
BCCC 280853 NULL 1
BCCC 280853 NULL 1
BCCC 280853 NULL 1
BCCC 281763 NULL NULL
BCCC 298015 NULL NULL
FCCC 233674 1 NULL
FCCC 233674 1 NULL
FCCC 233674 1 NULL
FCCC 233674 1 NULL
FCCC 233674 1 NULL
FCCC 233674 1 NULL
FCCC 269466 1 NULL
FCCC 269466 1 NULL
FCCC 269466 1 NULL
FCCC 288199 1 NULL
FCCC 288270 NULL 1
FCCC 288270 NULL 1
FCCC 288270 NULL 1
FCCC 288270 1 NULL
FCCC 288270 1 NULL

Given this data I would like to end up with:

BCCC 1 2
FCCC 4 1

Any help would be appreciated. I’m new to SQL and feel pretty inept.

Christy Geyer

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-03-19 : 15:25:41

Try this Christy:

SELECT agency, SUM(Pre_Trial) AS SUM_Pre_Trial, SUM(Post_Trial) AS SUM_Post_Trial
FROM (
SELECT DISTINCT jmmain.agency,
archrg.book_id,
case when[Bondstatus]='ACTI' Or [BondStatus]='BKRL' Or [BondStatus]='OR' Or [Bondstatus]='PR' then 1 end AS Pre_Trial ,
case when[Bondstatus]='SENT' Or [BondStatus]='DISM' then 1 end AS Post_Trial
FROM ARCHRG INNER JOIN jmmain ON ARCHRG.book_id = jmmain.book_id
WHERE (((jmmain.bkstatus)='A') AND ((ARCHRG.chrgdesc)<>'OUT OF COUNTY') AND ((ARCHRG.fel_misd)='F'))
/*order by jmmain.agency, archrg.book_id*/) z
GROUP BY agency
ORDER BY agency

I think that will do what you want. I have highlighted the text I added in bold. The DISTINCT clause in the original query forces it to ignore duplicates of the same output row. This query is then used as a subquery for the outer query, which sums the Pre_Trial and Post_Trial fields.

I have also commented out the original ORDER BY clause, as these are not permitted in subqueries.

There are 10 types of people in the world, those that understand binary, and those that don't.

cjg
Starting Member

4 Posts

Posted - 2010-03-19 : 16:03:41

Thanks! That does it for me.

Christy Geyer

cjg
Starting Member

4 Posts

Posted - 2010-03-23 : 14:08:48

This was extermely helpful but I have a question. What is the z for on the where clause?

SELECT agency, SUM(Pre_Trial) AS SUM_Pre_Trial, SUM(Post_Trial) AS SUM_Post_Trial
FROM (
SELECT DISTINCT jmmain.agency,
archrg.book_id,
case when[Bondstatus]='ACTI' Or [BondStatus]='BKRL' Or [BondStatus]='OR' Or [Bondstatus]='PR' then 1 end AS Pre_Trial ,
case when[Bondstatus]='SENT' Or [BondStatus]='DISM' then 1 end AS Post_Trial
FROM ARCHRG INNER JOIN jmmain ON ARCHRG.book_id = jmmain.book_id
WHERE (((jmmain.bkstatus)='A') AND ((ARCHRG.chrgdesc)<>'OUT OF COUNTY') AND ((ARCHRG.fel_misd)='F'))
/*order by jmmain.agency, archrg.book_id*/) z
GROUP BY agency
ORDER BY agency

Christy Geyer

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-23 : 14:11:27

its short name for derived table you created in query called alias

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

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-03-23 : 16:20:22

quote:
Originally posted by cjg

This was extermely helpful but I have a question. What is the z for on the where clause?

Glad I could help Christy.

The 'z' is an alias. It can be used to reference the rowset returned by the sub query.

There are 10 types of people in the world, those that understand binary, and those that don't.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources