-1

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;
Rick James
  • 135,179
  • 13
  • 127
  • 222
Ahn DAE YONG
  • 3
  • 2
  • 1
    Pictures are not safe in question. Provide sample data as editable text at least (the best way - as CREATE TABLE + INSERT INTO, or as online fiddle). – Akina Nov 30 '20 at 09:50
  • This is pivot which does not support by MySQL/MariaDB. Use conditional aggregation or dynamic SQL. – Akina Nov 30 '20 at 09:51
  • 1
    Welcome to SO. Please see [Why should I provide an MCRE for what seems to me to be a very simple SQL query](http://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query) - but at the same time, seriously consider handling issues of data display in application code. – Strawberry Nov 30 '20 at 09:52
  • @Strawberry sorry i Not good at this site, Thanks for the advice – Ahn DAE YONG Nov 30 '20 at 10:03
  • For MariaDB, see See https://stackoverflow.com/a/56670844/1766831 – Rick James Nov 01 '21 at 02:25

1 Answers1

0

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.

GMB
  • 216,147
  • 25
  • 84
  • 135