sql count statistics 1

[craigcjr]

I have a table (200,000+ records) with the following fields: Ticket, Member, Response (20 different responses available), Locator, and Resptime (date and time field). I would like to create another table with Response counts: that list (date, locator, and a field for each response (ie R1 count, R2 count, R3 count etc..)grouped by date and locator. Thanks in advance

 

[r937]

[tt]select [date]
, locator
, sum(iif(Response='R1',1,0)) as R1count
, sum(iif(Response='R2',1,0)) as R2count
...
, sum(iif(Response='R20',1,0)) as R20count
from yourtable
group
by [date]
, locator[/tt]

rudy

 

[craigcjr]

rudy, thanks for your fast reply, when I try to use your script, I get a syntax error

select respdate
, locator
, sum(iif((right(left(packet,23),2)='10',1,0)) as R10count
from responses
group
by respdate
, locator

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

also once I get the data I want to make a new table with it so I can link my asp web page to show the statistic info. Sorry for the stupid questions but I'm learning more and more of SQL scripting. Thank you very much

 

[r937]

unbalanced parens

there's two of 'em after iif

remove one

 

[jere42]

Lookup the T-SQL statement SELECT...INTO to create the table

 

[craigcjr]

thank everyone for your responses but I'm still having a hell of a time getting past the iff syntax error. The response in in a field called packet, that includes other info, but as you can see the response code (two numbers)part is parsed out. I'm wanting for R10count to give me a total count of how many times this locator had a ticket that he responded to with a 10 and a given day, and so on for the 19 other types of reponses he can make.

 

[JamesLean]

TSQL doesn't have the IIF function, you need to use CASE:

select [date]
     , locator
     , sum(CASE WHEN Response='R1' THEN 1 ELSE 0 END) as R1count
     , sum(CASE WHEN Response='R2' THEN 1 ELSE 0 END) as R2count
    ...
     , sum(CASE WHEN Response='R20' THEN 1 ELSE 0 END) as R20count
  from yourtable
group 
    by  [date]
     , locator

--James

 

[r937]

my bad

thanks, james

i was AFK all day

 

[craigcjr]

Thanks everybody, I'm up and running and everything is great.