Multiple Counts In One Query

I have a field with entires like this:

0003123120090O
0003123120091N
0003123120092R
0003123120093R
0003123120094N
0003123120080O
0003123120080O
0003123120070O
0003123120070O
0003123120070O
0003123120070O
0003123120060O

I need to get a total count of all records for 2009, a count of all records with N as the last character for 2009 and a count for R as the last character for 2009

I can do so with 3 separte SELECT COUNT(*) queries, but I was wondering if there was a way to get all three counts in one query. I tried this and it didn't work

Code:

---

select fieldname
    (select count(*) from tablename
    where fieldname like '%2009%' As total),   
    (select count(*) from tablename
    where fieldname like '%N%'
    and  fieldname like '%2009%' As NoLoads),
    (select count(*) from tablename
    where fieldname like '%R%'
    and fieldname like '%2009%' As Revisions)
from tablename

---

Is this possible or do I just suck it up and do three separate queries?

If the format is consistent they you can do

select sum(case col when substring(col,8,4)='2009' and substring(col,13,1)='R' then 1 else 0) as '2009R'
sum(case col when substring(col,8,4)='2009' and substring(col,13,1)='N' then 1 else 0) as '2009N'
sum(case col when substring(col,8,4)='2009' then 1 else 0) as '2009'
from table

The length of the entry is consistent.

Presuming I change 'col' with the name of the actual field, and table wit the name of the actual table, I get this when I run it

Quote:

---

Originally Posted by Query Analyser Error

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '='.

---

I have had trouble with queries when I properly space them out, so I made it look all nice an pretty

Code:

---

select
sum(case col when substring(col,8,4) = '2009' and substring(col,13,1) = 'R' then 1 else 0) as '2009R'
sum(case col when substring(col,8,4) = '2009' and substring(col,13,1) = 'N' then 1 else 0) as '2009N'
sum(case col when substring(col,8,4) = '2009' then 1 else 0) as '2009'
from table

---

But got the same error.

Thank you for your response. At least now I believe there is a way.

Moe1950, If you ran the query exactly as it is displayed, you are missing the commas between columns:

Code:

---

select
  sum(case col when substring(col,8,4) = '2009' and substring(col,13,1) = 'R' then 1 else 0) as '2009R'
, sum(case col when substring(col,8,4) = '2009' and substring(col,13,1) = 'N' then 1 else 0) as '2009N'
, sum(case col when substring(col,8,4) = '2009' then 1 else 0) as '2009'
from table

---

I ran it like this and still got the error

Code:

---

select
  sum(
      case col
      when substring(col,8,4) = '2009'
      and substring(col,13,1) = 'R'
      then 1 else 0) as '2009R',
  sum(
      case col
      when substring(col,8,4) = '2009'
      and substring(col,13,1) = 'N'
      then 1 else 0) as '2009N',
  sum(
      case col
      when substring(col,8,4) = '2009'
      then 1 else 0) as '2009'
from table

---

Okay, try removing the COL from just after each CASE:

Code:

---

select
  sum(
      case 
      when substring(col,8,4) = '2009'
      and substring(col,13,1) = 'R'
      then 1 else 0) as '2009R',
  sum(
      case 
      when substring(col,8,4) = '2009'
      and substring(col,13,1) = 'N'
      then 1 else 0) as '2009N',
  sum(
      case 
      when substring(col,8,4) = '2009'
      then 1 else 0) as '2009'
from table

---

I believe there are 2 forms of the CASE expression. If you use CASE col-name, the WHEN can only contain a Value which is checked for equality with the CASE col-name.

The other form just has CASE and then the WHEN does all the checking (which is what this query is trying to do).

When I run this

Code:

---

select
  sum(
      case 
      when substring(col,8,4) = '2009'
      and substring(col,13,1) = 'R'
      then 1 else 0) as '2009R',
  sum(
      case 
      when substring(col,8,4) = '2009'
      and substring(col,13,1) = 'N'
      then 1 else 0) as '2009N',
  sum(
      case 
      when substring(col,8,4) = '2009'
      then 1 else 0) as '2009'
from table

---

I no longer get the "Incorrect syntax near '='." message. Now I get

Quote:

---

Originally Posted by Query Analyser Error

Server: Msg 170, Level 15, State 1, Line 6
Line 6: Incorrect syntax near ')'.

---

It is a different error message so I figure I'm making progress. :D

Hopefully this will be the final fix. And END to complete the CASE expression:

Code:

---

select
  sum(
      case 
      when substring(col,8,4) = '2009'
      and substring(col,13,1) = 'R'
      then 1 else 0
      end) as '2009R',
  sum(
      case 
      when substring(col,8,4) = '2009'
      and substring(col,13,1) = 'N'
      then 1 else 0
      end ) as '2009N',
  sum(
      case 
      when substring(col,8,4) = '2009'
      then 1 else 0
      end ) as '2009'
from table

---

The basic syntax for the CASE expression is:

Code:

---

CASE
      WHEN comparison THEN something
                      ELSE something-else
END

---

Got it...I'm such a nudge. I can't believe I made such a flippin' rookie mistake.

Adding END fixed it.

Thanks!