Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 410
  • Last Modified:

SQL Sum () Having / Group By

SQL 2005 / 2008 : I have a simple sql query with 3 columns. I need to group by Column : GenericCode and to generate the Sum of function for Column B and Column C.

For each group of  GenericCode -> I need to generate Sum (DispensedQty) and Sum(340BCost)

Im struggling with Syntax,
select [GenericCode],[DispensedQty],[340BCost] from [Nov]
order by [GenericCode] asc

Open in new window

0
chokka
Asked:
chokka
  • 7
  • 4
1 Solution
 
sdstuberCommented:
elect genericcode,sum(dispensedqty),sum([340Bcost]) from nov
group by genericcode
order by genericcode
0
 
sdstuberCommented:
if you needed to filter the results of the sums you could add a HAVING clause, but based on the question above, it wouldn't apply here
0
 
chokkaAuthor Commented:
Actually .. let me put down an example !!

Currently in the Database

GenericCode              DispensedQty                  340BCost
30                                   10                                    10
30                                   20                                    30
678                                 10                                    5
678                                 45                                    67  
678                                 30                                    45

Expected Result is


GenericCode              DispensedQty                  340BCost
30                                   10                                    10
30                                   20                                    30
                                 Sum: 30                                   40

678                                 10                                    5
678                                 45                                    67  
678                                 30                                    45
                                 Sum:85                         Sum:117
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
sdstuberCommented:
ah....  that's different!  

but easily addressed,  just use the "with rollup" clause


select genericcode,sum(dispensedqty) qtysum,sum([340Bcost]) costsum from nov
group by genericcode with rollup
order by grouping(genericcode),genericcode
0
 
sdstuberCommented:
oops, that's not right either,  will try again
0
 
sdstuberCommented:
select
case when grouping([340BCost]) = 0 then
        cast(genericcode as char(6))
     when grouping(genericcode) = 0 then
        'Sum:'
     else
        'Total:' end codes,
     sum(dispensedqty) qtysum,
     sum([340Bcost]) costsum
from nov
group by genericcode,dispensedqty,[340Bcost]  with rollup
having  grouping(dispensedqty) + grouping([340BCost]) in(0, 2)
order by grouping(genericcode),genericcode,grouping(dispensedqty) + grouping([340BCost])
0
 
chokkaAuthor Commented:
@sdstuber, Thanks !! I learned new syntax - Rollup

This is an example of your query output

GenericCode      QtySum          CostSum
30                        30                    40
678                     85                   117

But , We expect a little more as ..


GenericCode              DispensedQty                  340BCost
30                                   10                                    10
30                                   20                                    30
                                 Sum: 30                                   40

678                                 10                                    5
678                                 45                                    67  
678                                 30                                    45
                                 Sum:85                         Sum:117



0
 
chokkaAuthor Commented:
Comment  ID: 36987097, So Terrific !! Awesome !! Thank you !! I need to verify the values before i close the question. Thanks
0
 
sdstuberCommented:
Here's another version that does formatting as you asked for...
this also eliminates the total line

select  case when grouping([340BCost])=0 then genericcode end genericcode,
case when grouping([340BCost]) = 0 then
        cast(sum(dispensedqty) as varchar(10))
     else  
        'Sum:' + cast(sum(dispensedqty) as varchar(10))
     end DispensedQty,    
     case when grouping([340BCost]) = 0 then
        cast(sum([340Bcost])as varchar(10))
     else  
        'Sum:' + cast(sum([340Bcost])as varchar(10)) end  [340BCost]
from nov
group by genericcode,dispensedqty,[340Bcost]  with rollup
having  grouping(dispensedqty) + grouping([340BCost]) in(0, 2)
and grouping(genericcode) = 0
order by nov.genericcode,grouping(dispensedqty) + grouping([340BCost])
0
 
chokkaAuthor Commented:
Thats terrific Logic, You should share with us !! Please,
0
 
sdstuberCommented:
the WITH ROLLUP allows the use of the grouping function.  The function returns 0 if that column is NOT being rolled and 1 if it is being rolled.

So,  

case when grouping([340BCost])=0 then genericcode end genericcode  

Open in new window


if we're not rolling up the cost, then print the code, if we are rolling up the costs, then code will be null (no else on the case, so NULL)

that's how we get the NULL first in the summation lines

{null}                          Sum:85                         Sum:117


case when grouping([340BCost]) = 0 then
        cast(sum(dispensedqty) as varchar(10))
     else  
        'Sum:' + cast(sum(dispensedqty) as varchar(10))
     end

Open in new window


if we are not rolling up the cost, then return the quantity
      (note,  SUM here will only be one record because of the GROUP BY clause)
if we are rolling up the cost, then return the sum along with the string "Sum:" to identify it.
      (note, SUM here will be the sum across multiple records because of the ROLLUP/GROUP)

same idea with the case on cost  - use cost as the check because it's the last column listed in the group by clause
the ordering makes a difference to rollup


group by genericcode,dispensedqty,[340Bcost]  with rollup

Open in new window


ok, this simple line is the real "engine" of the whole query.

rollup will cause the normal GROUP BY logic to run normally,  but then it will
apply the aggregates to the columns in sets

first, all of them, (this is the normal group by) :  group by  genericcode,dispensedqty,[340Bcost]
then  drop the first column  : group by dispensedqty,[340Bcost]
then drop the first two columns : group by [340Bcost]
then drop the first three columns : group by "nothing"  -  apply sum to ALL of the rows
and so on, but we only have 3 columns, so that's as far at the roll ups can go.

we don't really want all of those sums so

having grouping(dispensedqty) + grouping([340BCost]) in(0, 2) 
    and grouping(genericcode) = 0

Open in new window


this says exclude grouping of the genericcode  (the total of ALL rows)  grouping will =1 for only that summation
and also exclude intermediate groups where we sum on just the cost,
we want the rows where cost and qty are summed as a group (1+1=2) and when they are left alone (0+0=0)


I hope that helps!
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 7
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now