How do I sum results columns (grand total).

  • I have a query that produces the number of visits and payments by month for a year and cannot figure out how to total the columns.  I use Enterprise Guide to produce the code. I am allowed to type additional code into the code window prior to running the query.

  • Im guessing you want something like this:

     

    Month     Visits     payments

    Jan          5           8

    Feb          8          7

    Mar          10          2

    Total       23          17

     

    if you are using a query like this:

    select [month],sum(visits) as visits,sum(payments) as payments group by [month]

     

    you would want to do this:

    select

    select [month],sum(visits) as visits,sum(payments) as payments group by [month] UNION select 'Total',sum(visits),sum(payments)

     

    Notice all I did was add a union with the same query underneath, but instead of a group by, I just return 'Total' Note:use the same where clause on both queries

  • Use UNION ALL rather than UNION. There's no possibility of duplicates between the two queries.

    UNION means SQL will take the two result sets, concatinate, then sort to remove duplicates. UNION ALL omits the last step.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • I think you should all look into the use of ROLLUP in the GROUP BY clause and GROUPING in the column list.

    Try

    select

    Case GROUPING([month])

    WHEN 0 THEN [month]

    WHEN 1 THEN 'Total'

    END as Month

    , sum(visits) as visits

    ,sum(payments) as payments

    from monthVisitPayments

    group by rollup([month])

Viewing 4 posts - 1 through 3 (of 3 total)

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