Gomez Gomez - 6 months ago 14
MySQL Question

Mysql - Count rows up to a defined value by default and group them

A little help: I have to count some numbers up to a defined value and group them by id. For example:

my query:

SELECT part_number,
NAME,
NAME2,
count(id) as tot_prod,
min(serial_number) as serie_min,
max(serial_number) as serie_max,
opt_db.Quant as qty_default
FROM my_db, opt_db
where my_db.NAME2 = '17EM_2' and my_db.name2=opt_db.vs
group by my_db.number order by my_db.SERIAL_NUMBER


my_db result:

+-------------+-------+-------+----------+-----------+-----------+---------+
| number | NAME | NAME2 | tot_prod | serie_min | serie_max | default |
+-------------+-------+-------+----------+-----------+-----------+---------+
| 312705 | 17E21 | 7EM_2 | 3 | 21895 | 21897 | 10|
| 311971 | 17E21 | 7EM_2 | 20 | 21900 | 21920 | 10|
| 311972 | 17E21 | 7EM_2 | 6 | 21925 | 21930 | 10|
+-------------+-------+-------+----------+-----------+-----------+---------+


But I want this
OUTPUT:

+-------------+-------+-------+-----+-------+----------+----------+--------+
| number | NAME | NAME2 | tot | PACK |serie_min |serie_max |default |
+-------------+-------+-------+-----+-------+----------+----------+--------+
| 312705 | 17E21 | 7EM_2 | 3 | 3 | 21895 | 21897 | 10 |
| 311971 | 17E21 | 7EM_2 | 20 | 10 | 21900 | 21910 | 10 |
| 311971 | 17E21 | 7EM_2 | 20 | 10 | 21911 | 21920 | 10 |
| 311972 | 17E21 | 7EM_2 | 6 | 6 | 21925 | 21930 | 10 |
+-------------+-------+-------+-----+-------+----------+----------+--------+

Answer

SOLVED:

SELECT GROUP_CONCAT(distinct m.SERIAL_NUMBER order by m.SERIAL_NUMBER ) as series, 
            m.partnumber,
            m.NAME,
            m.NAME2,
            count(distinct m.id) as tot_prod, 
            min(m.serial_number) as serie_min, 
            max(m.serial_number) as serie_max, 
            e.Quantidade as qty_default,
            e.qty_faltam
            FROM my_db as m,  op_db  as e
            where m.NAME = 'nameExample' and  m.name2=e.name2 and m.partnumber = e.part_num
            group by m.partnumber order by series