Search code examples
sqlsql-serverjoinsum

nest two queries: sum & Join

How do I nest the following two queries?

I am trying to sum Duration from table scrap_log and filter via Reason but I want to use the matching string from table scrap_reasons where it holds the same product code integers.

SELECT Reason, SUM(Duration) FROM scrap_log GROUP by Reason

SELECT scrap_reasons.description, scrap_reasons.code
FROM  scrap_reasons
JOIN scrap_log ON scrap_log.Reason=scrap_reasons.code

I tried many different ways of doing this.

scrap_log

Reason Duration
10 20
10 40
11 40
13 33
13 33
11 2

scrap_reasons

code description
10 Bad Color
11 Bad Shape
13 Bad Size
14 Bad etc..

OUTPUT

Total Description
60 Bad Color
42 Bad Shape
66 Bad Size
Solution 
  • select   sum(scrap_log.Duration) as total
        ,scrap_reasons.description
from     scrap_log join scrap_reasons on scrap_reasons.code = scrap_log.Reason
group by scrap_reasons.description
    total description
    60 Bad Color
    42 Bad Shape
    66 Bad Size

    Fiddle