CODEWITHSUNDEEP
sqlsql-server
Ganthur
Ganthur

Reputation: 41

SQL Count number of times value appears in particular column in another table

Ok here is my problem I am trying to create a view that counts how many times a particular value is used in another table.

My View is like this 

SELECT 
table1.id AS code_id,
table2.asset_standard_id,
       Count(table2.pkey) AS COUNT
FROM table1  LEFT JOIN table2 
ON table2.code_id = table1.Id
where table1.code_type = (select pkey from imtbl_code_type where imtbl_code_type.id = 'A-Problem') and table2.asset_standard_id = '25209-45MEO'
GROUP BY table1.id,
table2.asset_standard_id
order by count

and this returns the count, but my problem is I also want to show zeros as well.

I do not think it is possible, but any help would be appreciated, I am at a loss.

Thanks!

Update (thanks for input :) ) This is a view that will be called by passing in the asset_standard_id, so I am not sure if I can do a On there.

Here is some sample for it the client will enter in an asset_standard_id of 25209-45MEO through a c# interface into Active Reports. this view will then execute to return all occurances that are found, plus ones that are not.

Table 1                        ||   table 2
---------------------------------
Code  | asset_standard_id       ||  Code
----------------------------------------------
c1      25209-45MEO            ||  c1
c3      25209-45MEO            ||  c2
c3      25209-45MEO            ||  c3

And what I would love to see in the results are:
code_id || asset_standard_id || count
c3           25209-45MEO         2
c1           25209-45MEO         1
c2           25209-45MEO         0

Upvotes: 1

Views: 2149

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269703

The condition on the second table needs to be in the on clause:

select t1.id AS code_id, t2.asset_standard_id,
       Count(t2.pkey) as cnt
from table1 t1 left join
     table2 t2
     on t2.code_id = t1.Id and
        t2.asset_standard_id = '25209-45MEO'
where t1.code_type = (select ict.pkey from imtbl_code_type ict where ict.id = 'A-Problem')
group by t1.id, t2.asset_standard_id
order by cnt;

Note that I changed the query to use table aliases. This makes the query easier to write and to read.

Upvotes: 1

Related Questions