Count Help

I want to display rows eg - displays number of properties for region

Auckland (12)
Wellington (2)
Christchurch (10)

1.
select distinct state from pcode

2.
SELECT     COUNT(PROPno) AS Expr1
FROM         Properties
WHERE     (state = @city)

@city = select statement 1


Combine 1 and 2 to get selected result.

LVL 7
skiltzAsked:
Who is Participating?
 
jrb1Commented:
Yes, RickBeebe is correct.  I was missing an end, but it has to be specified.  No brackets on the column alias.

select a.state, sum(case when properties.state is null then 0 else 1 end case) as count
from (select distinct state from pcode) a
left outer join properties
on a.state = properties.state
group by a.state
0
 
Nick UpsonPrincipal Operations EngineerCommented:
select state, count(*) from properties group by state
0
 
skiltzAuthor Commented:
But state might not exsist in properties
0
Network Scalability - Handle Complex Environments

Monitor your entire network from a single platform. Free 30 Day Trial Now!

 
jrb1Commented:
SELECT     state, COUNT(PROPno) AS Expr1
FROM         Properties
WHERE     state in (select distinct state from pcode)
group by state
0
 
skiltzAuthor Commented:
same as above there are 30 states but presently there are only 8 states which have properties...

idea I would like to return all 30 states

Auckland (0)
Christchurch (3)
bl barh (0)
0
 
jrb1Commented:
select pcode.state, sum(case when properties.state is null then 0 else 1) count
from pcode
left outer join properties
on pcode.state = properties.state
group by pcode.state
0
 
jrb1Commented:
sorry...left out the end

select pcode.state
      , sum(case when properties.state is null then 0 else 1 end) as count
from pcode
left outer join properties
on pcode.state = properties.state
group by pcode.state
0
 
skiltzAuthor Commented:
Ok cool almost there... so this returns all the states but the count numbers are incorrect..

if I do

 SELECT     COUNT(state) AS Expr1
FROM         Properties
WHERE     (state = 'ashburton')

there I get 3 records....but your state above shows 75 :(

0
 
jrb1Commented:
What counts do these give?

select count(*) from Properties where state = 'ashburton'

select count(*) from pcode where state = 'ashburton'

select count(PROPno) from Properties where state = 'ashburton'
0
 
skiltzAuthor Commented:
3
25
3

Right because in table pcode 'ashburton' is there 25 times because 'Ashburton' has many suburbs eg.. so we need a a DISTINCT?
0
 
jrb1Commented:
select a.state, sum(case when properties.state is null then 0 else 1) count
from (select distinct state from pcode) a
left outer join properties
on a.state = properties.state
group by a.state
0
 
skiltzAuthor Commented:

Error in list of function arguments: ')' not recognized.
Unable to parse query text.

All should it be 1) as count?

0
 
paulo111Commented:
Yeah and I beleive you may also have to wrap it in brackets [count].

Could be wrong.

sum(case when properties.state is null then 0 else 1) as [YourCount]
0
 
RickBeebeCommented:
Need to "END" your case statement

select a.state, sum(case when properties.state is null then 0 else 1 END) [count]
from (select distinct state from pcode) a
left outer join properties
on a.state = properties.state
group by a.state
0
 
awking00Commented:
I don't think you can use the word "count" as an alias since it is a reserved keyword.
0
 
jrb1Commented:
ah, you could be right.  probably with the brackets it would be ok. but CNT would be just as good.
0
 
skiltzAuthor Commented:
Thanks heaps for your help... this worked in the  end

SELECT     a.state, SUM(CASE WHEN properties.state IS NULL THEN 0 ELSE 1 END) AS count
FROM         (SELECT DISTINCT state
                       FROM          pcode) AS a LEFT OUTER JOIN
                      Properties ON a.state = Properties.state
GROUP BY a.state
0
 
skiltzAuthor Commented:
how do I then combine the fields?

a.state + count as combined?

0
 
jrb1Commented:
just concatenate:

SELECT     a.state + ' (' + SUM(CASE WHEN properties.state IS NULL THEN 0 ELSE 1 END) + ')'
FROM         (SELECT DISTINCT state
                       FROM          pcode) AS a LEFT OUTER JOIN
                      Properties ON a.state = Properties.state
GROUP BY a.state
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.