Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
saivina2920
Creator
Creator

How to group by and sum particular field from two different table

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...

5 Replies
pooja_prabhu_n
Creator III
Creator III

Hi,

refer the attached qvw file.

Totat2 calculation, there are two price for Material PIN101. Hence i am getting 5721+252= 5973

T1.PNG

Hope this helps.

Thanks,

Pooja

saivina2920
Creator
Creator
Author

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

saivina2920
Creator
Creator
Author

Also, pls. send your sample excel file where you referred...

saivina2920
Creator
Creator
Author

any update.

pooja_prabhu_n
Creator III
Creator III

Hi,

Please find the sample excel file.