CODEWITHSUNDEEP
sqlgroup-byhivesum
Caerus
Caerus

Reputation: 674

SQL: Sums over Combinations

Suppose I have a sales table like this:

store_id    industry_id    cust_id    amount    gender    age
       1            100       1000       1.00       M      20
       2            100       1000       2.05       M      20
       3            100       1000       3.15       M      20
       4            200       2000       5.00       F      30
       5            200       2000       6.00       F      30

Here's the code to generate this table:

CREATE TABLE t1(
    store_id int,
    industry_id int,
    cust_id int,
    amount float,
    gender char,
    age int
);
INSERT INTO t1 VALUES(1,100,1000,1.00, 'M',20);
INSERT INTO t1 VALUES(2,100,1000,2.05, 'M',20);
INSERT INTO t1 VALUES(3,100,1000,3.15, 'M',20);
INSERT INTO t1 VALUES(4,200,2000,5.00, 'F',30);
INSERT INTO t1 VALUES(5,200,2000,6.00, 'F',30);

Suppose we want to find the sum of amounts spent by each age/gender combination. Something like this:

gender    age    total_amount
    M      20            6.20
    F      30           11.00

The following code does not work (it throws an "Operand should contain 1 column(s)" error):

SELECT
    ('M','F') as gender
    , (20,30) as age 
    , (SUM(CASE WHEN gender='M' AND age=20 THEN amount END)
    , SUM(CASE WHEN gender='F' AND age=30 THEN amount END)) as total_amount
FROM t1;

What's the best way to do this?

Upvotes: 1

Views: 40

Answers (3)

VBoka
VBoka

Reputation: 9083

Maybe something like this:

SELECT gender
       , age 
       , SUM(amount) as total_amount
FROM t1
where (gender = 'M' and age = 20)
      or (gender = 'F' and age = 30)
group by gender, age

Upvotes: 0

Sahar Rezazadeh
Sahar Rezazadeh

Reputation: 333

use this:

Select gender, age, sum(amount ) as total_amout
from t1
group by gender,age

Upvotes: 0

GMB
GMB

Reputation: 222432

we want to find the sum of amounts spent by each age/gender combination

I think you just want aggregation:

select gender, age, sum(amount) total_amount
from t1
group by gender, age

Demo on DB Fiddle:

gender | age | total_amount
:----- | --: | :-----------
M      |  20 | 6.2         
F      |  30 | 11

Upvotes: 1

Related Questions