CODEWITHSUNDEEP
sqlsql-server
Morpheus
Morpheus

Reputation: 3523

Summarizing a table in SQL

I have the following table:

MedicineID  Type    PlantId category
1223456     ABC      P1          A
7821632     DEF      P2          B
5436710     GHI      P1          D
6743210     ABC      P3          A
4321793     THE      P4          D
7285743     ABC      P1          B
4143521     DEF      P3          A
5234345     GHI      P2          D
5132451     FGE      P1          B
1235432     REF      P4          A
5652315     GHI      P3          D
6733115     ABC      P2          B
7752242     DEF      P3          A
2652225     GHI      P2          D
6242352     ABC      P1          B

I have 7 different types of medicine = [ABC DEF GHI THE FGE REF XYZ ] And each can be in 4 different categories. The medicines are made in 4 different plants. All the plants have the capacity to make all the medicines. I want to know looking at the table how many are different medicines are being made in each plant each type and each category. My resultant table is supposed to look like this. 

Plant   Type     A      B      D
P1      ABC      1      2      0
P1      DEF      0      0      0
P1      GHI      0      0      1
P1      THE      0      0      0
P1      FGE      0      1      0
P1      REF      0      0      0
P1      XYZ      0      0      0
P2      ABC      0      1      0
P2      DEF      0      1      0
P2      GHI      0      0      2
P2      THE      0      0      0
P2      FGE      0      0      0
P2      REF      0      0      0
P2      XYZ      0      0      0
P3      ABC      1      0      0
P3      DEF      2      0      0
P3      GHI      0      0      1
P3      THE      0      0      0
P3      FGE      0      0      0
P3      REF      0      0      0
P3      XYZ      0      0      0
P4      ABC      0      0      0
P4      DEF      0      0      0
P4      GHI      0      0      0
P4      THE      0      0      1
P4      FGE      0      0      0
P4      REF      1      0      0
P4      XYZ      0      0      0

I am unsure which commands to even use to get started. Any help in the right direction will be appreciated.

Upvotes: 2

Views: 63

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269553

@vkp's answer is correct. The following might have somewhat better performance (assuming an index on tplant(type, plantid, category):

select p.plantid, t.type, tp.A, tp.B, tp.D
from (select distinct type from typetable) t cross join
     (select distinct plantid from tablename) p outer apply
     (select sum(case when t.category = 'A' then 1 else 0 end) as A,
             sum(case when t.category = 'B' then 1 else 0 end) as B,
             sum(case when t.category = 'D' then 1 else 0 end) as D
      from tplant tp
      where tp.type = t.type and tp.plantid = p.plantid
     ) tp;

Upvotes: 2

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

If the number of categories are fixed, you can simply use conditional aggregation.

select tplant.plantid,ttype.type,
count(case when t.category='A' then t.plant end) A,
count(case when t.category='B' then t.plant end) B,
count(case when t.category='D' then t.plant end) D
from (select distinct type from typetable) ttype --replace typetable with the table that has all the types
cross join (select distinct plantid from tablename) tplant
left join tablename t on t.plantid=tplant.plantid and t.type=ttype.type
group by tplant.plantid,ttype.type

Upvotes: 5

Related Questions