CODEWITHSUNDEEP
sqlsql-server-2005
hype
hype

Reputation: 55

SQL grouping and sum

I would like to know how to get the following result set out of my current result set.

My query is like this:

SELECT SUBSTRING(Col1, 1, 3) As 'Series', b.Col2, cast(c.Price * c.Qty as money) AS Total
from tableName a
inner join ....
...
WHERE date between '1/1/2012' and '1/31/2012'
AND B IN ('C50', 'C51', 'C52', 'C53', 'C54')
GROUP BY Col1, b.Col2, c.Price, c.Qty

And returns this result set:

Series       Col2        Total
----------   ----------  ----------
105          C50         5.00
105          C50         15.00
105          C53         20.00
105          C53         20.00
105          C53         20.00

And this is how I would like it to work:

Series       Col2        Total
----------   ----------  ----------
105          C50         20.00
105          C53         60.00

Thank you

Upvotes: 1

Views: 109

Answers (3)

Amy B
Amy B

Reputation: 110111

Group by the computed column : SUBSTRING(Col1, 1, 3) .

SELECT sub.Series, sub.Col2, SUM(sub.Total)
FROM
(
SELECT SUBSTRING(Col1, 1, 3) As 'Series', b.Col2, cast(c.Price * c.Qty as money) AS Total
from tableName a inner join ....
  ...
WHERE date between '1/1/2012' and '1/31/2012'
  AND B IN ('C50', 'C51', 'C52', 'C53', 'C54') 
) as sub
GROUP BY sub.Series, sub.Col2

Upvotes: 0

Mithrandir
Mithrandir

Reputation: 25337

Thats easy. Group just using Col1 and Col2 as grouping criteria and sum up you expression CAST(c.Price * c.Qty as money):

SELECT 
   SUBSTRING(Col1, 1, 3) As 'Series', 
   b.Col2, 
   SUM(CAST(c.Price * c.Qty as money)) AS Total
FROM tableName a
inner join ....
...
WHERE date between '1/1/2012' and '1/31/2012'
AND B IN ('C50', 'C51', 'C52', 'C53', 'C54')
GROUP BY Col1, b.Col2

Upvotes: 0

Andomar
Andomar

Reputation: 238086

Just group on the unaggregated columns, like:

SELECT SUBSTRING(Col1, 1, 3) As 'Series', b.Col2, 
    sum(cast(c.Price * c.Qty as money)) AS Total
from tableName a
inner join ....
...
WHERE date between '1/1/2012' and '1/31/2012'
AND B IN ('C50', 'C51', 'C52', 'C53', 'C54')
GROUP BY Col1, b.Col2

Upvotes: 1

Related Questions