CODEWITHSUNDEEP
sqlpostgresqljoincountsum
Hadi
Hadi

Reputation: 727

count total items, sold items (in another table reference by id) and grouped by serial number

I have a table of items in the shop, an item may have different entries with same serial number (sn) (but different ids) if the same item was bought again later on with different price (price here is how much did a single item cost the shop)

 id |  sn  | amount | price 
----+------+--------+-------
  1 | AP01 |    100 |     7
  2 | AP01 |     50 |     8
  3 | X2P0 |    200 |    12
  4 | X2P0 |     30 |    18
  5 | STT0 |     20 |    20
  6 | PLX1 |    200 |    10

and a table of transactions

 id | item_id | price 
----+---------+-------
  1 |       1 |    10
  2 |       1 |     9
  3 |       1 |    10
  4 |       2 |    11
  5 |       3 |    15
  6 |       3 |    15
  7 |       3 |    15
  8 |       4 |    18
  9 |       5 |    22
 10 |       5 |    22
 11 |       5 |    22
 12 |       5 |    22

and transaction.item_id references items(id)

I want to group items by serial number (sn), get their sum(amount) and avg(price), and join it with a sold column that counts number of transactions with referenced id

I did the first with

select i.sn, sum(i.amount), avg(i.price) from items i group by i.sn;

  sn  | sum |         avg         
------+-----+---------------------
 STT0 |  20 | 20.0000000000000000
 PLX1 | 200 | 10.0000000000000000
 AP01 | 150 |  7.5000000000000000
 X2P0 | 230 | 15.0000000000000000

Then when I tried to join it with transactions I got strange results

select i.sn, sum(i.amount), avg(i.price) avg_cost, count(t.item_id) sold, sum(t.price) profit from items i left join transactions t on (i.id=t.item_id) group by i.sn;

  sn  | sum |      avg_cost       | sold | profit 
------+-----+---------------------+------+--------
 STT0 |  80 | 20.0000000000000000 |    4 |     88
 PLX1 | 200 | 10.0000000000000000 |    0 | (null)
 AP01 | 350 |  7.2500000000000000 |    4 |     40
 X2P0 | 630 | 13.5000000000000000 |    4 |     63

As you can see, only the sold and profit columns show correct results, the sum and avg show different results than the expected

I can't separate the statements because I am not sure how can I add the count to the sn group which has the item_id as its id?

select 
    j.sn, 
    j.sum, 
    j.avg, 
    count(item_id) 
from (
    select 
        i.sn, 
        sum(i.amount), 
        avg(i.price) 
    from items i 
    group by i.sn
) j 
left join transactions t 
on (j.id???=t.item_id);

Upvotes: 1

Views: 110

Answers (1)

GMB
GMB

Reputation: 222582

There are multiple matches in both tables, so the join multiplies the rows (and eventually produces wron results). I would recommend pre-joining, then aggregating:

select 
    sn, 
    sum(amount) total_amount, 
    avg(price) avg_price, 
    sum(no_transactions) no_transactions
from (
    select 
        i.*, 
        (
            select count(*) 
            from transactions t 
            where t.item_id = i.id
        ) no_transactions
    from items i
) t
group by sn

Upvotes: 1

Related Questions