Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ernie99
Starting Member
12 Posts |
 Posted - 2009-03-13 : 19:23:59
|
| Hi everyone, can you please advise whether this can easily be done?I have a csv file of error messages related to sending emails. Each row contains an email address column and a status column explaining why the email send failed. There is only ever three possible values in the status column. I want to create an sql statement that returns a dataset of distinct email addresses with a new column containing a count of each type of status. For example the dataset might be:Address StatusA StatusB StatusCemail@email.com 2 1 0email1@email.com 1 0 5I have an sql statement that returns a count of each group but I'm not sure how to create and populate the new columns. For example here's what my current statement might returnAddress ItemCountemail@email.com 2email@email.com 1email1@email.com 1email1@email.com 5Here's my statement so far.select Address, Status, count(*) as ItemCount from BadRecips.csv group by Address, Status order by AddressThanks in advance to anyone who can help. |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2009-03-14 : 11:47:48
|
I didn't quite follow the part about doing a select statement from a csv file. Nonetheless, your select statement would probably be something like thisselect address, count(case when status='A' then 1 end) as StatusA, count(case when status='B' then 1 end) as StatusB, count(case when status='C' then 1 end) as StatusCfrom ...group by address |
 |
|
|
ernie99
Starting Member
12 Posts |
Posted - 2009-03-14 : 12:05:35
|
| That works perfectly, thanks very much for your help. |
|
|
|
|
|
|
New column for each group