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_expense
    GROUP BY entity,description
    ORDER 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
    --,description
    CASE 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)

You must be logged in to reply to this topic. Login to reply