1

I have 2 tables, customer and transaction.

Customer:
--------------------------------------------
ID | Name            | Tel
--------------------------------------------
1  | Peter           | 123 4567
2  | John            | 456 1234
3  | Alice           | 789 4561
4  | Amy             | 741 8525

Transaction:
--------------------------------------------
CustID | Books | Pens  | Ruler
--------------------------------------------
  1    |   2   |  0    |   1
  2    |   1   |  0    |   0
  1    |   0   |  3    |   0
  1    |   0   |  0    |   1
  2    |   1   |  1    |   1
  3    |   0   |  2    |   2

I need the following 

Results:
-------------------------------------------------------------------
ID | Name          | Tel            | Books | Pens  | Ruler
-------------------------------------------------------------------
1  | Peter         | 123 4567       |   2   |   3   |   2
2  | John          | 456 1234       |   2   |   1   |   1
3  | Alice         | 789 4561       |   0   |   2   |   2
4  | Amy           | 741 8525       |   0   |   0   |   0

Basically it will sum the Books, Pens and Ruler of the same Customer.

I've tried:

                        $sql = "select 
                                    `customer`.id,
                                    `custmaster`.name,
                                    `custmaster`.tel,
                                    `transaction`.id,
                                    `transaction`.books,
                                    `transaction`.pens,
                                    `transaction`.ruler,
                                from `customer` 
                                left join `transaction` 
                                on `customer`.id=`transaction`.custid 
                                ORDER BY `customer`.id ASC";

But display none. :( I do understand that I needed the sum() function somewhere. Anybody can help?

Sykes Tang
  • 31
  • 1

2 Answers2

0

Use SUM and GROUP BY.

SELECT c.id, c.name, c.tel, SUM(t.books) as books, SUM(t.pens) AS pens, SUM(t.ruler) AS ruler
FROM customer AS c
LEFT JOIN transactions AS t ON c.id = t.custid
GROUP BY c.id
ORDER BY c.id
Barmar
  • 741,623
  • 53
  • 500
  • 612
0

Try this way

select 
    `customer`.id,
    `custmaster`.name,
    `custmaster`.tel,
    `transaction`.id,
    sum(`transaction`.books) as books,
    sum(`transaction`.pens) as pens,
    sum(`transaction`.ruler) as ruler,
from `customer` 
left join `transaction` 
on `customer`.id=`transaction`.custid
Group by `customer`.id,`customer`.Name
ORDER BY `customer`.id ASC";
Barmar
  • 741,623
  • 53
  • 500
  • 612
Mukesh Kalgude
  • 4,814
  • 2
  • 17
  • 32
  • Do I need to have 2 group by? or can I just group by `customer`.id will do. I sort of test both, both give same results. – Sykes Tang Aug 27 '15 at 06:13
  • No point in selecting `transaction.id`, since you're aggregating all the transactions. – Barmar Aug 27 '15 at 06:15
  • @SykesTang Strict SQL says that you have to list all the non-aggregated columns in the `GROUP BY` clause. But MySQL allows you to leave them out, so you only need the columns that define the group. – Barmar Aug 27 '15 at 06:17