Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have two different SQL table.
In that, i want to group by and need to display the Sum of total which is corresponding row.
How to combine the two tables and do the group by for mentioning item...?
pls. find the attached sample SQL table data and require final output report...
Hi,
refer the attached qvw file.
Totat2 calculation, there are two price for Material PIN101. Hence i am getting 5721+252= 5973
Hope this helps.
Thanks,
Pooja
Thanks for your help.
I compose SQL Query as mentioned below.
Shall i use the below query.
SELECT COALESCE(p.PRNo,q.Project) AS Project,
COALESCE(p.PRItem,q.ProjectItem) AS ProjectItem,
COALESCE(q.TotalQty,0) AS Qty,
COALESCE(Total1,0) AS Total1,
COALESCE(Total2,0) AS Total2
FROM
(
SELECT Project,ProjectItem,SUM(Quantity) AS TotalQty
FROM table2
GROUP BY Project,ProjectItem
)q
FULL JOIN (
SELECT PRNo,PRItem,
SUM(CASE WHEN Material LIKE 'ALM%' OR Material LIKE 'AKC%' THEN CAST(Price AS decimal(20,2)) ) END) AS Total1,
SUM(CASE WHEN Material LIKE 'P%' OR Material LIKE '6%' THEN CAST(Price AS decimal(20,2)) END) AS Total2
FROM table1
GROUP BY PRNo,PRItem
)p
ON p.PRNo = q.Project
AND p.PRItem = q.ProjectItem
Also, pls. send your sample excel file where you referred...
any update.
Hi,
Please find the sample excel file.