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

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

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

    • 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