CODEWITHSUNDEEP
c#sqlsql-server-2008
TNA
TNA

Reputation: 616

Adding Count Column in sql query

I have two table called Production and Chart as below.

Production

Name Layer
CDV  TSK
CDV  USV
M1   OSK

Chart

Name
L1_CDV_TSK
L1_CDV_TSK
L1_M1_OSK

I have to produce output like this:

Name Layer Count
CDV  TSK    2
CDV  USV    0
M1   OSK    1

// bse L1_CDV_TKK and L1_M1_OSK are in the Charttable.

How could I write a SQL query to achieve this?

This is my attempt, but it cannot see every row in the Prodution table

Select 
    p.Name, p.layer, Count(*) as test
from 
    Production p, Chart c 
where 
    c.chartname like '%'+ p.name+'_'+p.layer + '%'
group by 
    p.Name, p.layer

Please advise. Thanks a lot!

Upvotes: 0

Views: 997

Answers (3)

Raphaël Althaus
Raphaël Althaus

Reputation: 60493

Select p.Name,p.layer,Count(c.chartname) as test
from Production p
left join Chart c on c.chartname like '%'+ p.name+'_'+p.layer + '%'
group by p.Name,p.layer

EDIT :

think you have a typo in your "Chart" list, or I don't understand the result you want... (TSK, TKK)

Upvotes: 5

Gordon Linoff
Gordon Linoff

Reputation: 1269447

First, you need to learn new join syntax:

Select p.Name, p.layer,Count(*) as test
from Production p join
     Chart c
     on c.chartname like '%'+ p.name+'_'+p.layer + '%'
group by p.Name, p.layer

Second, you are filtering out the one with the count of zero.

To get what you want, try:

Select p.Name, p.layer,
       sum(case when c.chartname like '%'+ p.name+'_'+p.layer + '%' then 1 else 0 end) as test
from Production p cross join
     Chart c
group by p.Name, p.layer

Upvotes: 0

Adam
Adam

Reputation: 26907

Try this:

Select p.Name,p.layer,Count(*) as test
from Production p
left join Chart c on c.chartname like '%'+ p.name+'_'+p.layer + '%'
group by p.Name,p.layer

Upvotes: 1

Related Questions