Question 265535 in Mysql Question posted in
The official MySQL documentation can be found here.

MySQL Grouping with value separations not working

I have a MySQL table that records item with item, make, model & serial number as follows :

item table

item_name make model serial_no qty
Computer HP Pro 300D 123ert34c10 1
Computer HP Pro 400D 123xgt34c110 1
Printer Canon ir1210 56tyu78 1
Computer HP Pro 400D 123xgt34c111 1
Printer Canon ir1215 gh90fr 1
UPS APC A200 apc5640400 1
UPS APC A300 apc5640401 1
Computer HP Pro 300D 123ert34c13 1

Then I need to group items by the item_name and make. And also needs to show all models & serial nos in the group summary as follows :

Desired Output

item_name make model serial_no qty
Computer HP Pro 300D, Pro 400D 123ert34c10, 123ert34c13, 123xgt34c110, 123xgt34c111 4
Printer Canon ir1210, ir1215 56tyu78, gh90fr 2
UPS APC A200, A300 apc5640400, apc5640401 2

I used the following simple query to do this.

SELECT item_name, make, model, serial_no, SUM(qty) AS qty FROM item GROUP BY item_name, make

But the query outs the following output without showing all models & serial nos under the relevant item_name & make.

Incorrect Output

item_name make model serial_no qty
Computer HP Pro 300D 123ert34c10 4
Printer Canon ir1210 56tyu78 2
UPS APC A200 apc5640400 2

Some models & serial nos were missing. What may going wrong. Can anyone help ?

Tags:

2

Answers


  1. 0

    When you GROUP BY you will get a single row for all the data grouped by given column but you will not get any summary or whatever of these data which is what you for some reason expected. This is actually correct for GROUP BY. If you want get all the details, you would need GROUP_CONCAT() function instead:

    SELECT 
    item_name, 
    make, 
    GROUP_CONCAT(DISTINCT model ORDER BY model ASC) AS model,
    GROUP_CONCAT(DISTINCT serial_no ORDER BY serial_no ASC) AS serial_no,
    SUM(qty) AS qty 
FROM 
    item
GROUP BY 
    item_name, 
    make;
    Login or Signup to reply.
  2. 0

    You can achieve the Desired_Output by using GROUP_CONCAT(). Here is the modified sql

    SELECT
    item_name,
    make,
    GROUP_CONCAT(DISTINCT model ORDER BY model) AS model,
    GROUP_CONCAT(DISTINCT serial_no ORDER BY serial_no) AS serial_no,
    SUM(qty) AS qty
FROM item
GROUP BY item_name, make;

    The DISTINCT ensures model and serial_no are included only once, while GROUP_CONCAT concatenates the results after they are sorted using ORDER BY

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search