-
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
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
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.
-
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!
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|