I know that my question would be duplicated but I really don’t know how to created sql which return results of sum with multiple join.
Tables I have
result_summary
x
num_bin id_summary count_bin 3 172 0 4 172 0 5 172 0 6 172 0 7 172 0 8 172 0 1 174 1 2 174 0 3 174 0 4 174 0 5 174 0 6 174 0 7 174 0 8 174 0 1 175 0summary_assembly
num_lot id_machine sabun date_work date_write id_product shift count_total count_fail count_good id_summary id_operation adfe 1 21312 2020-11-25 2020-11-25 1 A 10 2 8 170 2000 adfe 1 21312 2020-11-25 2020-11-25 1 A 1000 1 999 171 2000 adfe 1 21312 2020-11-25 2020-11-25 2 A 100 1 99 172 2000 333 1 21312 2020-12-06 2020-12-06 1 A 10 2 8 500 2000 333 1 21312 2020-11-26 2020-11-26 1 A 10000 1 9999 174 2000 333 1 21312 2020-11-26. 2020-11-26 1 A 100 0 100 175 2000 333 1 21312 2020-12-06 2020-12-06 1 A 10 2 8 503 2000 333 1 21312 2020-12-07 2020-12-07 1 A 10 2 8 651 2000 333 1 21312 2020-12-02 2020-12-02 1 A 10 2 8 178 2000employees
sabun name_emp3532 Kim12345 JS4444 Gilsoo21312 Wayn Hahn123 Lee too333 JDinfo_product
id_product name_product 1 typeA 2 typeBmachine
id_machine id_operation name_machine 1 2000 name1 2 2000 name2 3 2000 name3 4 3000 name1 5 3000 name2 6 3000 name3 7 4000 name1 8 4000 name2 query
select S.id_summary, I.name_product, M.name_machine, E.name_emp, S.sabun, S.date_work, S.shift, S.num_lot, S.count_total, S.count_good, S.count_fail, sum(case num_bin when '1' then count_bin else 0 end) as bin1, sum(case num_bin when '2' then count_bin else 0 end) as bin2, sum(case num_bin when '3' then count_bin else 0 end) as bin3, sum(case num_bin when '4' then count_bin else 0 end) as bin4, sum(case num_bin when '5' then count_bin else 0 end) as bin5, sum(case num_bin when '6' then count_bin else 0 end) as bin6, sum(case num_bin when '7' then count_bin else 0 end) as bin7, sum(case num_bin when '8' then count_bin else 0 end) as bin8 from result_assembly as Rjoin summary_assembly as S on R.id_summary = S.id_summary join employees as E on S.sabun = E.sabun join info_product as I on S.id_product = I.id_product join machine as M on S.id_machine = M.id_machine where I.id_product = '1' and E.sabun='21312' and S.shift = 'A' and S.date_work between '2020-11-10' and '2020-12-20' group by S.id_summary, E.name_emp, S.num_lot,I.name_product,M.name_machineorder by S.id_summary;result
id_summary name_product name_machine name_emp sabun date_work shift num_lot count_total count_good count_fail bin1 bin2 bin3 bin4 bin5 bin6 bin7 bin8170 TypeA name1 Kim 21312 2020-11-25 A adfe 10 8 2 1 1 0 0 0 0 0 0171 TypeA name1 Kim 21312 2020-11-25 A adfe 1000 999 1 1 1 0 0 0 0 0 0174 TypeA name1 Kim 21312 2020-11-26 A 333 10000 9999 1 1 1 0 0 0 0 0 0175 TypeA name1 Kim 21312 2020-11-26 A 333 100 100 0 0 0 0 0 0 0 0 0178 TypeA name1 Kim 21312 2020-12-02 A 333 10 8 2 1 1 0 0 0 0 0 0179 TypeA name1 Kim 21312 2020-12-02 A 333 10 8 2 1 1 0 0 0 0 0 0180 TypeA name1 Kim 21312 2020-12-02 A 333 10 8 2 1 1 0 0 0 0 0 0181 TypeA name1 Kim 21312 2020-12-02 A 333 10 8 2 1 1 0 0 0 0 0 0182 TypeA name2 Kim 21312 2020-12-02 A 333 10 8 2 1 1 0 0 0 0 0 0186 TypeA name2 Kim 21312 2020-12-06 A 333 10 8 2 1 1 0 0 0 0 0 0193 TypeA name2 Kim 21312 2020-12-06 A 333 10 8 2 0 0 0 0 0 0 0 0194 TypeA name2 Kim 21312 2020-12-06 A 333 10 8 2 0 0 0 0 0 0 0 0195 TypeA name2 Kim 21312 2020-12-06 A 333 10 8 2 0 0 0 0 0 0 0 0196 TypeA name2 JS 21312 2020-12-06 A 333 10 8 2 0 0 0 0 0 0 0 0197 TypeA name2 JS 21312 2020-12-06 A 333 10 8 2 0 0 0 0 0 0 0 0198 TypeA name2 JS 21312 2020-12-06 A 333 10 8 2 0 0 0 0 0 0 0 0199 TypeA name2 JS 21312 2020-12-06 A 333 10 8 2 0 0 0 0 0 0 0 0200 TypeA name2 JS 21312 2020-12-06 A 333 10 8 2 0 0 0 0 0 0 0 0expected output(when sum by num_lot)
num_lot count_total count_good count_fail bin1 bin2 bin3 bin4 bin5 bin6 bin7 bin8 adfe 323 300 23 22 1 0 0 0 0 0 0 333 4312 4300 12 10 2 0 0 0 0 0 0 All of them were modified from original one because they were non-English, so there would be typo.
Here now I need to sum by num_lot, name_product or sabun. id_summary is unique.
Thanks
Advertisement
Answer
As expected in the comments: It seems like you simple need a subquery which groups your table by the column num_lot
SELECT num_lot, SUM(count_total), SUM(count_good) -- some more SUM()FROM ( --<your query>) s GROUP BY num_lotIt was asked in the comments what the s stands for: A subquery needs an alias, an identifier. Because I didn’t want to think about a better name, I just called the subselect s. It is the shortcut for AS s