Counting the values in a single column

Question

I'm using the sqldf package in R and am trying to find the count of values which are 1 and 2 in a single column. My data looks like:

> head(d)
       bid status
1  201-300      1
2  201-300      1
3 901-1000      2
4  601-700      1
5  801-900      1
6  801-900      2

I am trying to find the count of status when it's equal to 1 and the count of status when it's equal to 2, and then have them in two separate columns.

So using the sqldf package in R, I ran the following code:

sqldf("SELECT bid, SUM(IF(status='2', 1,0)) AS 'won', SUM(IF(status='1', 1,0)) AS 'lost', COUNT(bid) FROM d GROUP BY bid")

However, I get the following error message.

Error in sqliteExecStatement(con, statement, bind.data) : 
  RS-DBI driver: (error in statement: no such function: IF)

Is this not possible with the sqldf package? Is there a way to get the desired results with another sql command in R? (or with plyr, reshape, or any other tool in R)

Answer 1

The IF is afaik a MySQL specific syntax while the error indicates you are talking to a SQLite database.

You should replace the IF with a CASE which would work on all DMBS's that are ANSI SQL-92 compliant.

SELECT  bid
        , SUM(CASE WHEN status = 1 THEN 1 ELSE 0 END) AS won
        , SUM(CASE WHEN status = 0 THEN 1 ELSE 0 END) AS lost
FROM    d
GROUP BY
        bid

Answer 2

The normal SQL for this would be to use CASE and SUM - I don't do MySQL but I'm assuming this should be valid syntax:

SELECT SUM(CASE WHEN Status = '2' THEN 1 ELSE 0 END) as 'won',
       SUM(CASE WHEN Status = '1' THEN 1 ELSE 0 END) as 'lost',
...