mardi 2 février 2016

Using sql to sum with multiple table calls

I'll get down to the point. So basically I have 3 tables structured as follows:

orders:

i_id |  o_id  |  quantity
-----+--------+----------
  1  |   1    |     5
  2  |   2    |     2
  1  |   3    |     3
  1  |   4    |     3
  2  |   5    |     4

orderinfos:

o_id  |  c_id
------+------------
 1    |   1
 2    |   2
 3    |   2
 4    |   1
 5    |   2

customers:

    c_id  |  name_id
----------+----------
     1    |   100001
     2    |   100002

then the resulting chart would be:

    name_id |   i_id   |  quantity
 -----------+----------+----------
 100001     |    1     |    8
 100002     |    2     |    6
 100002     |    1     |    3

So basically, you have a summary of something (in this case, orders) with their quantity, and then where each order has the customer id and the item name associated. Then the resulting chart would be something that gives the quantity per customer, per item, in descending order by the customer. My first implementation was this:

select quantCust.custIdName, quantCust.itemId, quantCust.quant
from
    (select O.i_id as itemId,
    C.name_id as custIdName,
    sum(O.quantity) as quant
    from orders as O, orderinfos as I, customers as C
    where O.o_id = I.o_id and I.c_id = C.c_id
    group by O.i_id, I.c_id) as quantCust
order by quantCust.custId, quantCust.quant desc;

which does not print the correct values.

Aucun commentaire:

Enregistrer un commentaire