How do I merge row that has the same value and total value with mysql query?

Question

My query sql like this :

SELECT a.transaction_number a.date, a.item_number, b.desc, a.variant_code, sum(a.quantity) AS total_qty, a.cost
FROM `items_details` AS a
JOIN `items` AS b ON b.id = a.item_number
WHERE a.item_number = 0101010
GROUP BY a.variant_code
ORDER BY transaction_number, variant_code

The result like this :

I want to make the table to be like this :

How can I do it?

You should do this in the application layer. Although possible in SQL, such reformatting is more appropriate in other tools. — Gordon Linoff, Aug 10 '18 at 12:07
Any time you have a GROUP BY clause, but no aggregating functions, you can be certain that something has gone very wrong somewhere! — Strawberry, Aug 10 '18 at 12:08
Invalid group by. Won't execute on newer MySQL versions (unless in compatibility mode), may return unpredictable results with older MySQL versions. The general GROUP BY rule says: If a GROUP BY clause is specified, each column reference in the SELECT list must either identify a grouping column or be the argument of a set function! — jarlh, Aug 10 '18 at 13:08
@Gordon Linoff I use Laravel Framework. I want this to be handled from sql so that pagination can run properly — moses toh, Aug 11 '18 at 14:27
@Strawberry I had update my question. So now there exist `sum` — moses toh, Aug 11 '18 at 14:29
A good rule of thumb when using aggregating functions is to be sure to include all non aggregated columns in the GROUP BY clause. — Strawberry, Aug 11 '18 at 14:39
@Strawberry What if I have a lot of non aggregated columns ? Do I have to group by all those columns? — moses toh, Aug 11 '18 at 14:45
Yes. It's not always necessary, but it's always good practice — Strawberry, Aug 11 '18 at 14:58
@Strawberry Okay. Then what is the solution to my problem? Can I use a sql query to make it like in figure 2? — moses toh, Aug 11 '18 at 15:06
You can, but it's ill advised. Better to handle display issues in application code — Strawberry, Aug 11 '18 at 15:15

score 1 · Accepted Answer · answered Aug 13 '18 at 14:01

1

Just for fun...

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(colA INT NOT NULL
,colB INT NOT NULL
,colC INT NOT NULL
,PRIMARY KEY(colA,colB,colC)
);

INSERT INTO my_table VALUES
(101,1001,31),
(101,1002,14),
(102,1001,15),
(102,1001,92),
(102,1002,65),
(103,1001,35),
(103,1002,89);

SELECT CASE WHEN @prevA=colA THEN '' ELSE colA END colA
     , CASE WHEN @prevB=colB THEN '' ELSE colB END colB
     , colC
     , @prevA:=colA
     , @prevB:=colB
  FROM my_table x
     , (SELECT @prevA:=null,@prevB:=null) vars
 ORDER
    BY x.colA, x.colB;

+------+------+------+--------------+--------------+
| colA | colB | colC | @prevA:=colA | @prevB:=colB |
+------+------+------+--------------+--------------+
| 101  | 1001 |   31 |          101 |         1001 |
|      | 1002 |   14 |          101 |         1002 |
| 102  | 1001 |   15 |          102 |         1001 |
|      |      |   92 |          102 |         1001 |
|      | 1002 |   65 |          102 |         1002 |
| 103  | 1001 |   35 |          103 |         1001 |
|      | 1002 |   89 |          103 |         1002 |
+------+------+------+--------------+--------------+

answered Aug 13 '18 at 14:01

Strawberry

33,750
13
40
57

Great. Thanks a lot – moses toh Aug 14 '18 at 02:54
Maybe you can help me again. Look at this : https://stackoverflow.com/questions/51832979/how-to-create-select-dynamic-fields-from-a-table-in-mysql – moses toh Aug 14 '18 at 02:55
You really need to familiarise yourself with some kind of presentation layer. These are all display issues and as such would be trivial to solve in application code – Strawberry Aug 14 '18 at 06:40
If I know the solution, I won't ask – moses toh Aug 14 '18 at 08:06
Well, PHP is popularly used in conjunction with mysql, so maybe take a look at that. – Strawberry Aug 14 '18 at 08:34

How do I merge row that has the same value and total value with mysql query?

1 Answers1