Results 1 to 9 of 9

Thread: Multiple Counts In One Query

  1. #1
    Join Date
    Dec 2009
    Posts
    79

    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?

  2. #2
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,922
    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

  3. #3
    Join Date
    Dec 2009
    Posts
    79
    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.

  4. #4
    Join Date
    Apr 2009
    Posts
    86
    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

  5. #5
    Join Date
    Dec 2009
    Posts
    79
    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

  6. #6
    Join Date
    Apr 2009
    Posts
    86
    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).

  7. #7
    Join Date
    Dec 2009
    Posts
    79
    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.

  8. #8
    Join Date
    Apr 2009
    Posts
    86
    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

  9. #9
    Join Date
    Dec 2009
    Posts
    79
    Got it...I'm such a nudge. I can't believe I made such a flippin' rookie mistake.

    Adding END fixed it.

    Thanks!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •