Generating Subtotals for each group

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

General SQL Server Forums

New to SQL Server Programming

Generating Subtotals for each group

Author

Topic

evanburen
Posting Yak Master

167 Posts

Posted - 2015-03-17 : 09:05:41

[code]SELECT
LTRIM(LoanAnalyst) AS [Loan Analyst]
,DischargeType AS [Discharge Type]
,CONVERT(varchar, DateCompleted, 101) AS [Date Completed]
,COUNT(ClaimID) As [Completions]
FROM
Claims
WHERE
DateCompleted IS NOT NULL
AND DateCompleted >= @DateCompletedBegin
AND DateCompleted < DATEADD(dd, 1, @DateCompletedEnd)
GROUP BY
DischargeType
,LoanAnalyst
,CONVERT(varchar, DateCompleted, 101)
ORDER BY
[Loan Analyst],
[Date Completed] DESC,
[Discharge Type][/code]

This produces this
[code]Loan Analyst Discharge Type Date Completed Completions
Bill Reid Type 1 3/3/2015 1
Bill Reid Type 1 2/11/2015 1
Bill Reid Type 2 3/11/2015 18
Bill Reid Type 3 3/11/2015 1
Bill Reid Type 4 3/11/2015 9
Tina Jones Type 1 3/10/2015 1
Tina Jones Type 2 3/6/2015 9
Tina Jones Type 3 3/6/2015 1
Tina Jones Type 4 3/4/2015 35
Paco Smith Type 2 3/3/2015 15
Paco Smith Type 3 3/3/2015 1
Paco Smith Type 3 2/27/2015 16
Paco Smith Type 3 2/27/2015 1
Paco Smith Type 4 2/27/2015 9
[/code]

I would like my results to look like this. I've tried using WITH ROLLUP but it doesn't give me the below. Thanks

[code]Loan Analyst Discharge Type Date Completed Completions
Bill Reid Type 1 3/3/2015 1
Bill Reid Type 1 2/11/2015 1
Bill Reid Type 2 3/11/2015 18
Bill Reid Type 3 3/11/2015 1
Bill Reid Type 4 3/11/2015 9
30
Tina Jones Type 1 3/10/2015 1
Tina Jones Type 2 3/6/2015 9
Tina Jones Type 3 3/6/2015 1
Tina Jones Type 4 3/4/2015 35
46
Paco Smith Type 2 3/3/2015 15
Paco Smith Type 3 3/3/2015 1
Paco Smith Type 3 2/27/2015 16
Paco Smith Type 3 2/27/2015 1
Paco Smith Type 4 2/27/2015 9
42
[/code]

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-17 : 10:45:19

with rollup is designed to do what you need. Please post the query you used when you tried with rollup and the results obtained that way

evanburen
Posting Yak Master

167 Posts

Posted - 2015-03-17 : 13:06:23

quote:
Originally posted by gbritton

with rollup is designed to do what you need. Please post the query you used when you tried with rollup and the results obtained that way

This the code with the ROLLUP function. It rolls it up or provides a total for every Loan Analyst, Discharge Type, Date Completed combination. I just want a sum for every change in Loan Analyst.

SELECT
      LTRIM(LoanAnalyst) AS [Loan Analyst]
      ,DischargeType AS [Discharge Type]
      ,CONVERT(varchar, DateCompleted, 101) AS [Date Completed]
      ,COUNT(ClaimID) As [Completions]
FROM
      Claims
WHERE
      DateCompleted IS NOT NULL
      AND DateCompleted >= '01/01/2015'
      AND DateCompleted < DATEADD(dd, 1, '03/17/2015')
GROUP BY
      DischargeType
      ,LoanAnalyst 
      ,CONVERT(varchar, DateCompleted, 101) WITH ROLLUP
ORDER BY
      [Loan Analyst],
      [Date Completed] DESC,
      [Discharge Type]

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-17 : 13:10:12

Oh, try grouping sets:

group by grouping sets
(
( DischargeType
,LoanAnalyst
,CONVERT(varchar, DateCompleted, 101)
)
)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources