How to Write a query using MYSQL

Question

I am working on query which is working fine , but its not working for some condition. Ideally, it should work, but I had no idea where I am going wrong. Here is example, there are three tables like capital machine and qc

Query:

select all_dates.value1 as `Date`, coalesce(`Outward1(B_Qty)`,`Outward2(B_Qty)`,`Outward3(B_Qty)`, '0') as `Outward` 
from 
(select distinct C_Date as value1 from capital where P2_Goods like '750%' and Monthname(C_Date)='April'  
union all
select distinct M_Date from machine  where J_Id like '%750' and Monthname(M_Date)='April' 
union all
select distinct QC_Date from qc where QC_Name like '750%'  and Monthname(QC_Date)='April'  
) as all_dates
left join( select C_Date, sum(PR) AS `Outward1(B_Qty)` 
from capital where P2_Goods like '750%' and Monthname(C_Date)='April' group by C_Date) 
as f on f.C_Date = all_dates.value1 
left join( select M_Date, (sum(PR)+sum(C_Skip)) AS `Outward2(B_Qty)` 
from machine  where J_Id like '%750' and Monthname(M_Date)='April'  group by M_Date) 
as h on  h.M_Date = all_dates.value1 
left join( select QC_Date, sum(PR) AS `Outward3(B_Qty)` 
from qc where QC_Name like '750%' and Monthname(QC_Date)='April' group by QC_Date) 
as k on  k.QC_Date = all_dates.value1 group by all_dates.value1

Main Purpose of Query is, Total of PR and C_Skip column from each table group by Date
Now, If there are same dates on two different tables then it should add the PR , but in this case it's not adding.Thats the issue.
Please help me out.Thanks in advance.

Utsav · Accepted Answer · 2016-05-11 06:22:30Z

4

Try this.

select date1,sum(outward) as outward from
    (
    select c_date as date1,pr as outward from capital
    union all
    select m_date as date1,pr+c_skip  as outward from machine
    union all
    select qc_date as date1,pr  as outward from qc
    ) t
group by date1

edited May 11, 2016 at 6:22

answered May 11, 2016 at 5:16

Utsav

8,1632 gold badges22 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user007 Over a year ago

It worked. Complex Query into Simple Query. Great job. Thanks a lot. Need to learn a lot.

Collectives™ on Stack Overflow

How to Write a query using MYSQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related