CODEWITHSUNDEEP
sqlpostgresql
Oleg Tarasenko
Oleg Tarasenko

Reputation: 9610

SQL: Aggregate counts of a related model

I have the following database schema:

Shop with fields: id, address, name

Product with fields: id, shop_id, category(enum: clothes, furniture, electronics)

Is there a way to write an efficient query to get the information about shops so the final query would produce a summary about each shop. For example:

name     clothes  furniture  electronics
shop1       5        3          100

Upvotes: 1

Views: 50

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270391

If you know the columns that you want, I would write this as:

select s.name, 
       sum( (p.category = 'clothes')::int ) as clothes,
       sum( (p.category = 'furniture')::int ) as furniture,
       sum( (p.category = 'electronic')::int ) as electronic
from shop s join
     product p
     on s.id = p.shop_id
group by s.name;

You might also consider crosstab() or dynamic SQL if you do not know the columns.

Upvotes: 0

Hary
Hary

Reputation: 5818

Try using Pivot table.

Sql Fiddle

Pivot:

PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output, and performs aggregations where they are required on any remaining column values that are wanted in the final output

Unpivot:

UNPIVOT performs the opposite operation to PIVOT by rotating columns of a table-valued expression into column values.

More about Pivot

select *
from
(
  select name, value
  from(
  select name, category
  from test t
  join shop s on s.id = t.shop_id) as s
  unpivot
  (
    value
    for col in (category)
  ) unp
) src
pivot
(
  count(value)
  for value in ([clothes], [furniture], [electronics])
) piv

Upvotes: 1

Fahmi
Fahmi

Reputation: 37473

use conditional aggregation:

select name, count(case when category='clothes' then 1 end) as clothes,
count(case when category='furniture' then 1 end) as furniture,
count(case when category='electronics' then 1 end) as electronic
from
(select name,category from shop inner join product 
on shop.id=product.shop_id)a
group by name

Upvotes: 1

Related Questions