# Have a total by Entity instead of Line total

• How could I turn this into a total by Entity rather that just having a line total by day. I would like to group all of the days for an entity

and produce a total.

Thanks.

`SELECT  entity        ,description        ,[1st] = SUM(CASE WHEN datepart(dd,effdate) = '1' THEN expamt ELSE 0 END)        ,[2nd] = SUM(CASE WHEN datepart(dd,effdate) = '2' THEN expamt ELSE 0 END)        ,[3rd] = SUM(CASE WHEN datepart(dd,effdate) = '3' THEN expamt ELSE 0 END)        ,[4th] = SUM(CASE WHEN datepart(dd,effdate) = '4' THEN expamt ELSE 0 END)        ,[5th] = SUM(CASE WHEN datepart(dd,effdate) = '5' THEN expamt ELSE 0 END)        ,[6th] = SUM(CASE WHEN datepart(dd,effdate) = '6' THEN expamt ELSE 0 END)        ,[7th] = SUM(CASE WHEN datepart(dd,effdate) = '7' THEN expamt ELSE 0 END)        ,[8th] = SUM(CASE WHEN datepart(dd,effdate) = '8' THEN expamt ELSE 0 END)        ,[9th] = SUM(CASE WHEN datepart(dd,effdate) = '9' THEN expamt ELSE 0 END)        ,[10th] = SUM(CASE WHEN datepart(dd,effdate) = '10' THEN expamt ELSE 0 END)        ,[11th] = SUM(CASE WHEN datepart(dd,effdate) = '11' THEN expamt ELSE 0 END)        ,[12th] = SUM(CASE WHEN datepart(dd,effdate) = '12' THEN expamt ELSE 0 END)        ,[13th] = SUM(CASE WHEN datepart(dd,effdate) = '13' THEN expamt ELSE 0 END)        ,[14th] = SUM(CASE WHEN datepart(dd,effdate) = '14' THEN expamt ELSE 0 END)        ,[15th] = SUM(CASE WHEN datepart(dd,effdate) = '15' THEN expamt ELSE 0 END)        ,[16th] = SUM(CASE WHEN datepart(dd,effdate) = '16' THEN expamt ELSE 0 END)        ,[17th] = SUM(CASE WHEN datepart(dd,effdate) = '17' THEN expamt ELSE 0 END)        ,[18th] = SUM(CASE WHEN datepart(dd,effdate) = '18' THEN expamt ELSE 0 END)        ,[19th] = SUM(CASE WHEN datepart(dd,effdate) = '19' THEN expamt ELSE 0 END)        ,[20th] = SUM(CASE WHEN datepart(dd,effdate) = '20' THEN expamt ELSE 0 END)        ,[21st] = SUM(CASE WHEN datepart(dd,effdate) = '21' THEN expamt ELSE 0 END)        ,[22nd] = SUM(CASE WHEN datepart(dd,effdate) = '22' THEN expamt ELSE 0 END)        ,[23rd] = SUM(CASE WHEN datepart(dd,effdate) = '23' THEN expamt ELSE 0 END)        ,[24th] = SUM(CASE WHEN datepart(dd,effdate) = '24' THEN expamt ELSE 0 END)        ,[25th] = SUM(CASE WHEN datepart(dd,effdate) = '25' THEN expamt ELSE 0 END)        ,[26th] = SUM(CASE WHEN datepart(dd,effdate) = '26' THEN expamt ELSE 0 END)        ,[27th] = SUM(CASE WHEN datepart(dd,effdate) = '27' THEN expamt ELSE 0 END)        ,[28th] = SUM(CASE WHEN datepart(dd,effdate) = '28' THEN expamt ELSE 0 END)        ,[29th] = SUM(CASE WHEN datepart(dd,effdate) = '29' THEN expamt ELSE 0 END)        ,[30th] = SUM(CASE WHEN datepart(dd,effdate) = '30' THEN expamt ELSE 0 END)        ,[31st] = SUM(CASE WHEN datepart(dd,effdate) = '31' THEN expamt ELSE 0 END)        ,[LineTotal] = SUM(expamt)   FROM freight_tracker_expense    GROUP BY entity,groupname,description,effdate  ORDER BY entity`
• Not 100% sure what you need, but probably:

`SELECT  entity        ,description        ,[Total] = SUM(expamt)FROM freight_tracker_expenseGROUP BY entity,descriptionORDER BY entity`

SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

• Ya that gets me a total, but would like to see the running days like I had, but create a total record within the construct I already

have. So after all of and Enties daily total have a TOTAL for ENTITY ....

Thanks...

• OK.  You said "instead of" so I figured you didn't want the other details in the new query.

Maybe try:

GROUP BY entity,groupname,description,effdate WITH ROLLUP

You can get rid of total lines you won't want to see using HAVING and GROUPING_ID, etc.

SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

• Ya that thru in some NULL records for totaling

Thanks.

• Yeah, you can test for NULLs in columns and/or GROUPING_ID to determine: which rows are total rows and which rows are detail rows; to exclude total rows you don't want; and to sort the total rows the way you want to.

SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

• Scott did you ever have a chance to re-write the cursor issue I put out there in this forum you where looking for the error table

schema at last post.

Thanks.

• I got it to work....

Thanks for suggestions..

`SELECT  -- entity        --,descriptionCASE WHEN GROUPING(entity) = 1 THEN 'Day Total' ELSE ISNULL(entity, 'Unknown') END as entity   -- CASE WHEN GROUPING(description) = 1 THEN 'Total Description' ELSE ISNULL(description, 'Unknown') END as description,[1st] = SUM(CASE WHEN datepart(dd,effdate) = '1' THEN expamt ELSE 0 END)        ,[2nd] = SUM(CASE WHEN datepart(dd,effdate) = '2' THEN expamt ELSE 0 END)        ,[3rd] = SUM(CASE WHEN datepart(dd,effdate) = '3' THEN expamt ELSE 0 END)        ,[4th] = SUM(CASE WHEN datepart(dd,effdate) = '4' THEN expamt ELSE 0 END)        ,[5th] = SUM(CASE WHEN datepart(dd,effdate) = '5' THEN expamt ELSE 0 END)        ,[6th] = SUM(CASE WHEN datepart(dd,effdate) = '6' THEN expamt ELSE 0 END)        ,[7th] = SUM(CASE WHEN datepart(dd,effdate) = '7' THEN expamt ELSE 0 END)        ,[8th] = SUM(CASE WHEN datepart(dd,effdate) = '8' THEN expamt ELSE 0 END)        ,[9th] = SUM(CASE WHEN datepart(dd,effdate) = '9' THEN expamt ELSE 0 END)        ,[10th] = SUM(CASE WHEN datepart(dd,effdate) = '10' THEN expamt ELSE 0 END)        ,[11th] = SUM(CASE WHEN datepart(dd,effdate) = '11' THEN expamt ELSE 0 END)        ,[12th] = SUM(CASE WHEN datepart(dd,effdate) = '12' THEN expamt ELSE 0 END)        ,[13th] = SUM(CASE WHEN datepart(dd,effdate) = '13' THEN expamt ELSE 0 END)        ,[14th] = SUM(CASE WHEN datepart(dd,effdate) = '14' THEN expamt ELSE 0 END)        ,[15th] = SUM(CASE WHEN datepart(dd,effdate) = '15' THEN expamt ELSE 0 END)        ,[16th] = SUM(CASE WHEN datepart(dd,effdate) = '16' THEN expamt ELSE 0 END)        ,[17th] = SUM(CASE WHEN datepart(dd,effdate) = '17' THEN expamt ELSE 0 END)        ,[18th] = SUM(CASE WHEN datepart(dd,effdate) = '18' THEN expamt ELSE 0 END)        ,[19th] = SUM(CASE WHEN datepart(dd,effdate) = '19' THEN expamt ELSE 0 END)        ,[20th] = SUM(CASE WHEN datepart(dd,effdate) = '20' THEN expamt ELSE 0 END)        ,[21st] = SUM(CASE WHEN datepart(dd,effdate) = '21' THEN expamt ELSE 0 END)        ,[22nd] = SUM(CASE WHEN datepart(dd,effdate) = '22' THEN expamt ELSE 0 END)        ,[23rd] = SUM(CASE WHEN datepart(dd,effdate) = '23' THEN expamt ELSE 0 END)        ,[24th] = SUM(CASE WHEN datepart(dd,effdate) = '24' THEN expamt ELSE 0 END)        ,[25th] = SUM(CASE WHEN datepart(dd,effdate) = '25' THEN expamt ELSE 0 END)        ,[26th] = SUM(CASE WHEN datepart(dd,effdate) = '26' THEN expamt ELSE 0 END)        ,[27th] = SUM(CASE WHEN datepart(dd,effdate) = '27' THEN expamt ELSE 0 END)        ,[28th] = SUM(CASE WHEN datepart(dd,effdate) = '28' THEN expamt ELSE 0 END)        ,[29th] = SUM(CASE WHEN datepart(dd,effdate) = '29' THEN expamt ELSE 0 END)        ,[30th] = SUM(CASE WHEN datepart(dd,effdate) = '30' THEN expamt ELSE 0 END)        ,[31st] = SUM(CASE WHEN datepart(dd,effdate) = '31' THEN expamt ELSE 0 END)        ,[Entity Total] = SUM(expamt)   FROM freight_tracker_expense   GROUP BY ROLLUP (entity)  --GROUP BY entity,groupname,description,effdate WITH ROLLUP    --GROUP BY entity,groupname,description,effdate  ORDER BY entity`
• This was removed by the editor as SPAM

Viewing 9 posts - 1 through 9 (of 9 total)