CODEWITHSUNDEEP
mysqlsql
user007
user007

Reputation: 75

How to Write a query using MYSQL

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

enter image description here

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.

Upvotes: 0

Views: 74

Answers (1)

Utsav
Utsav

Reputation: 8093

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

Upvotes: 4

Related Questions