Warm tip: This article is reproduced from serverfault.com, please click

MYSQL Group by DATA NOT individually

发布于 2020-11-30 09:48:58

This is My Data:

enter image description here

I want select data Like This ▼

enter image description here

I try select that in group by but is not individually data show.

and this is my code

select 
date_format((select receive_contract_datetime from worklist_info where id = worklist_id), '%y.%m.%d') as receive_datetime,
(select trade_name from trade_info where id = (select worklist_trade_id from worklist_info where id = worklist_id)) as trade_name,

(select staff_name from staff_info where id = 
(select account_staff_id from account_info where id = 
(select worklist_account_id from worklist_info where id = worklist_id))) as worklist_writer,

date_format((select worklist_output_plan_date from worklist_info where id = worklist_id), '%y.%m.%d') as output_plan_date,
(select worklist_project_name from worklist_info where id = worklist_id) as prj_name,
worklist_sub_process_id,
count(worklist_sub_process_id),
sum(worklist_sub_state),
-- (sum(worklist_sub_process_id = 1)*2) as laser_count, worklist_sub_state


-- sum(worklist_sub_process_id = 1) as laser_count,
-- if(sum(worklist_sub_process_id = 1) > 0,count(IF(worklist_sub_process_id = 1, if(worklist_sub_state = 0,1,null), null)),-1) as laser_wait,
-- if(sum(worklist_sub_process_id = 1) > 0,count(IF(worklist_sub_process_id = 1, if(worklist_sub_state = 1,1,null), null)),-1) as laser_run,
-- if(sum(worklist_sub_process_id = 1) > 0,count(IF(worklist_sub_process_id = 1, if(worklist_sub_state = 2,1,null), null)),-1) as laser_end,

(select worklist_comment from worklist_info where id = worklist_id) as worklist_comment,
(select worklist_lot from worklist_info where id = worklist_id) as  lot_number
 from worklist_info_sub group by worklist_id,worklist_sub_process_id;
Questioner
Ahn DAE YONG
Viewed
0
GMB 2020-11-30 19:39:25

You seem to pivot your dataset. For this, you can use conditional aggregation:

select col_master_id,
    sum(col_semi_id = 1) as col_semi_1_count,
    sum(case when col_semi_id = 1 then col_state else 0 end) as col_semi_1_state,
    sum(col_semi_id = 2) as col_semi_2_count,
    sum(case when col_semi_id = 2 then col_state else 0 end) as col_semi_2_state,
    sum(col_semi_id = 3) as col_semi_3_count,
    sum(case when col_semi_id = 3 then col_state else 0 end) as col_semi_3_state,
from mytable
group by col_master_id

I don't see how your query relates to your data. This answer is based on your sample data and desired results, not on your query.