group values in sql server view

Question

Hi i have a view that contains this:

sku quantity price discount
123   4       10     YES
123   1       10     YES
123   1       10     NO

the table have a lot of fields thats just a example what im trying to achieve is to group the sku by refering the quantity and discount column so it will look like this:

sku quantity price discount
123   5       10     YES
123   1       10     NO

so in this case there was 5 items with discount= YES same sku but other one with same sku 123 but quantity 1 and discount= NO, how i can group or sum that to make this work?

thank you

`SELECT sku, SUM(quantity), AVG(price), discount FROM mytable GROUP BY sku, discount`? — Giorgos Betsos, Sep 17 '15 at 19:27
no the price will always be the same because they are the same item with same sku and price is fixed — alexistkd, Sep 17 '15 at 19:27
possible duplicate of [How to Sum multiple lines in sql](http://stackoverflow.com/questions/9600587/how-to-sum-multiple-lines-in-sql) — Tab Alleman, Sep 17 '15 at 19:31

score 0 · Answer 1 · answered Sep 17 '15 at 21:18

You could use window functions depending on your version of SQL Server:

Create Sample Table:

CREATE TABLE mytable (
    sku INT
    ,quantity INT
    ,price MONEY
    ,discount VARCHAR(5)
    );

insert into mytable values
(123, 4, 10, 'YES'),
(123, 1, 10, 'YES'),
(123, 1, 10, 'NO');

Query:

select distinct sku,
       sum(quantity) OVER (partition by sku,discount order by sku) as quantity,
       max(price) OVER (partition by sku,discount order by sku) as price,
       discount       
from mytable

Result:

+-----+----------+-------+----------+
| sku | quantity | price | discount |
+-----+----------+-------+----------+
| 123 |        1 |    10 | NO       |
| 123 |        5 |    10 | YES      |
+-----+----------+-------+----------+

SQL Fiddle Demo

group values in sql server view

1 Answers1