CODEWITHSUNDEEP
sqlinner-join
Kman00
Kman00

Reputation: 17

Select a column twice with different conditions from one table and using join with two other tables

I will try to do this properly this time

So I have 3 SQL tables, Production,Resources,Asset and they look like this:

Asset:

ID     Name
------------
1     Line1
2     Line2
3     Line3

Resource:

ID   Asset_ID
------------
1      1
2      1
3      2
4      3
5      3

Production:

Resource_ID  Quantity    Type
------------------------------
1               5        Scrap
3               10       Scrap
1               6        Rework
3               3        Rework
1               15       Scrap

What I'm trying to is to get to this result:

Name   Scrap    Rework
-----------------------
Line1    20       6
Line2    10       3
Line3    0        0

Basically I need to grab each line name and then display the total scrap count and total rework count in the next two columns. Now I have done part of it, I have no problem getting what I need for either Scrap or Rework one at the time, where I get stuck is when I want to get both of them in one table in different columns. I'm using the following right now:

SELECT a.ID, a.Name, d.Resource_ID, SUM(d.Quantity) [Scrap]
FROM Production d inner join SubResource r ON d.Resource_ID = s.ID 
inner join Asset a ON a.ID = r.Asset_ID
WHERE Type = 'Scrap' group by a.ID, a.Name, d.Resource_ID

Which gives me:

Name   Scrap
------------
Line1    20
Line2    10
Line3    0

I know I need to sub-queries here(One to get scrap and one for rework) and I tried to do research on it but getting all these inner joins to work with the sub-queries confused the heck out of me. I tried a few and I kept getting the total number of scraps and total number of reworks for ALL lines which clearly isn't correct. I'd appreciate if someone can point me to the right direction. Thank you

Upvotes: 1

Views: 154

Answers (1)

SqlZim
SqlZim

Reputation: 38023

The traditional cross tab / conditional aggregation version of a pivot() would be like so:

select
    a.id
  , a.Name
  , sum(case when [Type]='Scrap' then d.Quantity else 0 end) as [Scrap]
  , sum(case when [Type]='Rework' then d.Quantity else 0 end) as [Rework]
from Asset a 
  left join Resource r 
    on a.id = r.Asset_id
  left join Production d 
      on d.Resource_id = r.id 
     and d.[Type] in ('Scrap','Rework')
group by 
    a.id
  , a.Name

test setup: http://rextester.com/LPMJB2638

returns:

+----+-------+-------+--------+
| id | Name  | Scrap | Rework |
+----+-------+-------+--------+
|  1 | Line1 |    20 |      6 |
|  2 | Line2 |    10 |      3 |
|  3 | Line3 |     0 |      0 |
+----+-------+-------+--------+

Upvotes: 2

Related Questions