Adding Count Column in sql query

Question

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!

GarethD · Accepted Answer · 2012-05-30 14:27:05Z

5

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)

edited May 30, 2012 at 14:27

GarethD

70k11 gold badges90 silver badges130 bronze badges

answered May 30, 2012 at 14:09

Raphaël Althaus

60.6k6 gold badges106 silver badges127 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Adam · Accepted Answer · 2012-05-30 14:10:29Z

1

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

answered May 30, 2012 at 14:10

Adam

27k8 gold badges65 silver badges85 bronze badges

Comments

Gordon Linoff · Accepted Answer · 2012-05-30 14:11:09Z

0

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

answered May 30, 2012 at 14:11

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

Adding Count Column in sql query

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related