Answered by:
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...Friday, August 3, 2018 11:27 AM
Answers
-
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.ProjectItemError : // 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.- Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Monday, August 6, 2018 4:29 AM -
User77042963 posted
SELECT COALESCE(p.Projectdefinition,cast(q.ProjectNo as varchar(1000))) AS Project,...
- Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Monday, August 6, 2018 2:23 PM
All replies
-
User77042963 posted
Post your table structure and sample data script on this page. Thanks.
Friday, August 3, 2018 1:46 PM -
User-1024101449 posted
Friday, August 3, 2018 2:01 PM -
User77042963 posted
Post your script here. Don't post a link and people may not click on your link at all.
Friday, August 3, 2018 2:14 PM -
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.ProjectItemError : // 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.- Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Monday, August 6, 2018 4:29 AM -
User77042963 posted
SELECT COALESCE(p.Projectdefinition,cast(q.ProjectNo as varchar(1000))) AS Project,...
- Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Monday, August 6, 2018 2:23 PM -
User-1024101449 posted
Thanks..
We identified the error which is same to you.
it's working..
Tuesday, August 7, 2018 4:58 AM