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

Question

User-1024101449 posted

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

Answer 1

User77042963 posted

Post your table structure and sample data script on this page. Thanks.

Answer 2

User-1024101449 posted

https://1drv.ms/u/s!AiSRcgO5FUmNbkiJ0PjtwsHp2cE

Answer 3

User77042963 posted

Post your script here. Don't post a link and people may not click on your link at all.

Answer 4

User-1024101449 posted

below is the SQL Query.

but, getting error when we execute the 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

Error : // SELECT COALESCE(p.PRNo,q.Project) AS Project,

Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.
Warning: Null value is eliminated by an aggregate or other SET operation.

Answer 5

User77042963 posted

SELECT COALESCE(p.Projectdefinition,cast(q.ProjectNo as varchar(1000))) AS Project,...

Answer 6

User-1024101449 posted

Thanks..

We identified the error which is same to you.

it's working..