0

Here is my table

+------+------+-------------+
| sid  | cid  | amount      |
+------+------+-------------+
| 32   | 1    | 500         |
+------+------+-------------+
| 33   | 1    | 300         |
+------+------+-------------+
| 32   | 2    | 500         |
+------+------+-------------+
| 33   | 2    | 400         |
+------+------+-------------+
| ..   | .    | ...         |
+------+------+-------------+
| ..   | .    | ...         |
+------+------+-------------+
| 32   | n    | 100         |
+------+------+-------------+
| 33   | n    | 100         |
+------+------+-------------+

In my view I want to show it in my view like

+------+----------+-----------+------+------------+------------+
| sid  | amount(1)| amount(2) |  ... | amount(n)  |Total amount|
+------+----------+-----------+------+------------+------------+
| 32   | 500      | 500       |  ... | 100        |1100        |
+------+----------+-----------+------+------------+------------+
| 33   | 300      | 400       |  ... | 100        |800         |
+------+----------+-----------+------+------------+------------+

How should I do it?

S. M. Shahinul Islam
  • 2,780
  • 4
  • 36
  • 68

1 Answers1

2

You can use GROUP_CONCAT, SUM and GROUP clause of SQL. However, there won't be multiple columns for amount. The list of amount for a sid will all be in one single column with values separated by comma.

SELECT sid,
       GROUP_CONCAT(amount) AS amount,
       SUM(amount) AS total
FROM mytbl
GROUP BY sid;
kums
  • 2,661
  • 2
  • 13
  • 16
  • Since you are going to use PHP, you can get the individual amounts from the amount column using something like: – kums Oct 19 '14 at 06:20
  • I second this answer for simplicities sake; unless you are using a database that supports pivots out of the box. – James Daniels Oct 19 '14 at 06:49