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
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 0
summary_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 2000
employees
sabun name_emp 3532 Kim 12345 JS 4444 Gilsoo 21312 Wayn Hahn 123 Lee too 333 JD
info_product
id_product name_product
1 typeA
2 typeB
machine
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 R join 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_machine order 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 bin8 170 TypeA name1 Kim 21312 2020-11-25 A adfe 10 8 2 1 1 0 0 0 0 0 0 171 TypeA name1 Kim 21312 2020-11-25 A adfe 1000 999 1 1 1 0 0 0 0 0 0 174 TypeA name1 Kim 21312 2020-11-26 A 333 10000 9999 1 1 1 0 0 0 0 0 0 175 TypeA name1 Kim 21312 2020-11-26 A 333 100 100 0 0 0 0 0 0 0 0 0 178 TypeA name1 Kim 21312 2020-12-02 A 333 10 8 2 1 1 0 0 0 0 0 0 179 TypeA name1 Kim 21312 2020-12-02 A 333 10 8 2 1 1 0 0 0 0 0 0 180 TypeA name1 Kim 21312 2020-12-02 A 333 10 8 2 1 1 0 0 0 0 0 0 181 TypeA name1 Kim 21312 2020-12-02 A 333 10 8 2 1 1 0 0 0 0 0 0 182 TypeA name2 Kim 21312 2020-12-02 A 333 10 8 2 1 1 0 0 0 0 0 0 186 TypeA name2 Kim 21312 2020-12-06 A 333 10 8 2 1 1 0 0 0 0 0 0 193 TypeA name2 Kim 21312 2020-12-06 A 333 10 8 2 0 0 0 0 0 0 0 0 194 TypeA name2 Kim 21312 2020-12-06 A 333 10 8 2 0 0 0 0 0 0 0 0 195 TypeA name2 Kim 21312 2020-12-06 A 333 10 8 2 0 0 0 0 0 0 0 0 196 TypeA name2 JS 21312 2020-12-06 A 333 10 8 2 0 0 0 0 0 0 0 0 197 TypeA name2 JS 21312 2020-12-06 A 333 10 8 2 0 0 0 0 0 0 0 0 198 TypeA name2 JS 21312 2020-12-06 A 333 10 8 2 0 0 0 0 0 0 0 0 199 TypeA name2 JS 21312 2020-12-06 A 333 10 8 2 0 0 0 0 0 0 0 0 200 TypeA name2 JS 21312 2020-12-06 A 333 10 8 2 0 0 0 0 0 0 0 0
expected 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_lot
It 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