Sum QTY within 2 tables

[erosche]

Dear Experts,

I have 2 tables as below and I want to sum the QTY for table MWOHED and also MWOPTR.
Then the result would be show as 'Result'.

Please help me to define the coding, as my coding will duplicate the sum qty for table mwohed.

Thanks in advanced.
[HTML]Table MWOHED
Sch# MO# Color# QTY
104988 3311403 Black 10
104988 3311404 Black 10
104988 3311405 Black 18
104988 3311406 Black 28
104988 3311407 Black 42
104988 3311408 Black 40
104988 3311409 Black 48
104988 3311410 Black 26[/HTML]

[HTML]Table MWOPTR
Workcenter MO# Report date QTY
11000 3311403 20070523 10
11000 3311404 20070523 10
11000 3311405 20070522 18
11000 3311406 20070522 18
11000 3311406 20070523 10
11000 3311407 20070522 2
11000 3311407 20070523 10
11000 3311407 20070523 20
11000 3311407 20070524 5
11000 3311407 20070602 2
11000 3311407 20070608 3
11000 3311408 20070523 10
11000 3311408 20070523 20
11000 3311408 20070524 5
11000 3311408 20070602 2
11000 3311408 20070608 3
11000 3311409 20070522 8
11000 3311409 20070523 10
11000 3311409 20070523 20
11000 3311409 20070524 5
11000 3311409 20070602 2
11000 3311409 20070608 3
11000 3311410 20070522 26
41000 3311403 20070523 9
41000 3311404 20070523 9
41000 3311405 20070522 18
41000 3311406 20070522 18
41000 3311406 20070523 9
41000 3311407 20070522 2
41000 3311407 20070523 9
41000 3311407 20070523 20
41000 3311407 20070524 5
41000 3311407 20070602 2
41000 3311407 20070608 3
41000 3311408 20070523 10
41000 3311408 20070523 20
41000 3311408 20070524 5
41000 3311408 20070602 2
41000 3311408 20070608 3[/HTML]
[HTML] Result
Sch# Color# Ttl_Order Qty Ttl_Cut Qty Ttl_Sew Qty
104988 Black 222 222 144
[/HTML]

[amitpatel66]

Could you please post your Query for my reference??

[pbmods]

Heya, Erosche.

Something like this, perhaps?

SELECT
        `MWOHED`.`Sch#`,
        `MWOHED`.`Color#`,
        SUM(`table`.`something`)
            AS `Ttl_Order_Qty`,
        etc.
    FROM
    (
            `MWOHED`
        LEFT JOIN
            `MWOPTR`
                USING
                    (`MO#`)
    )
    GROUP BY
        `MWOHED`.`Sch#`,
        `MWOHED`.`Color#`
 

The idea is to use the SUM() function to specify which columns you want summed (from the names of your table fields, I was unable to determine exactly which fields go here).

Then you use the GROUP BY clause to tell MySQL which fields have to be unique in order to denote a unique entity. In other words, in the example above, each row must have a unique `Sch#` and `Color#` in order to get its own row in the result set; otherwise, it will be grouped (and its values SUM()'ed) into the row that has the same `Sch#` and `Color#`.

[erosche]

Could you please post your Query for my reference??

Hi...

I'm using winsql for my coding. And below is my query :

select sch#, color, sum(mwohed.ttl_order), sum(mwoptr.ttl_cut)
from mwohed, mwoptr
where sch# = '104988'
and mwohed.MO# = mwoptr.MO#
and workcenter like '11%'  --FOr Total Cut qty
group by sch#, color
 

With above query, I will get duplicate total order qty because of the MO# is repeated at table MWOPTR when I need to get total cut qty (workcenter 11000).
Then I also don't know how to get the total sew qty (workcenter 41xxxx) together in 1 query.

Could you help ?

Thanks a lot.... ^_^

[erosche]

Heya, Erosche.

Something like this, perhaps?

SELECT
        `MWOHED`.`Sch#`,
        `MWOHED`.`Color#`,
        SUM(`table`.`something`)
            AS `Ttl_Order_Qty`,
        etc.
    FROM
    (
            `MWOHED`
        LEFT JOIN
            `MWOPTR`
                USING
                    (`MO#`)
    )
    GROUP BY
        `MWOHED`.`Sch#`,
        `MWOHED`.`Color#`
 

The idea is to use the SUM() function to specify which columns you want summed (from the names of your table fields, I was unable to determine exactly which fields go here).

Then you use the GROUP BY clause to tell MySQL which fields have to be unique in order to denote a unique entity. In other words, in the example above, each row must have a unique `Sch#` and `Color#` in order to get its own row in the result set; otherwise, it will be grouped (and its values SUM()'ed) into the row that has the same `Sch#` and `Color#`.

Hey Moderator....

Thanks a lot for the query, but it still can't serve my request....
There's 3 column that I need to sum, which is Total order qty, Total cut qty (which is from table MWOPTR, field workcenter 11000), & Total sew qty (which is from table MWOPTR, field workcenter 41000).
Understand the usage of SUM() and group by, but I just can't think of correct query to combine those data into 1 correct query.

Please help....

Thanks...

[bestdnd]

as i understand, u want all the details from mwohead and the sum of qty from mwoptr:

select mwohed.*, sum(mwoptr.qty)
 
from mwohed as mwohed
 
left join mwoptr as mwoptr on mwohed."MO#"=mwoptr."MO#"
 
group by
"Sch#", "MO#", color, mwohed.qty