CODEWITHSUNDEEP
r
ATMathew
ATMathew

Reputation: 12866

Counting the values in a single column

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)

Upvotes: 2

Views: 1049

Answers (6)

G. Grothendieck
G. Grothendieck

Reputation: 270170

if is not valid SQLite syntax. Try this:

> sqldf("select bid, sum(status=1) lost, sum(status=2) won, count(*) count 
+ from d group by bid")
       bid lost won count
1  201-300    2   0     2
2  601-700    1   0     1
3  801-900    1   1     2
4 901-1000    0   1     1

Upvotes: 1

Salman Arshad
Salman Arshad

Reputation: 272376

Edit: the question was tagged mysql but I am not sure is this is the case

Have a look at MySQL Control Flow Functions. You could use the IF construct (MySQL specific) or CASE WHEN (ANSI compatible) operator:

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


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

Upvotes: 2

Brian Diggs
Brian Diggs

Reputation: 58845

Since you said you could be interested in a plyr based solution, I can give that:

ddply(d, .(bid), summarise, won = sum(status==2), 
                            lost = sum(status==1), count = length(bid))

Upvotes: 1

Raphaël VO
Raphaël VO

Reputation: 2640

try this:

select count(bid) as 'bid_status_1' from d where bid_status = 1 union select count(bid) as 'bid_status_2'from d where bid_status = 2

Upvotes: 0

JNK
JNK

Reputation: 65197

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',
...

Upvotes: 2

Lieven Keersmaekers
Lieven Keersmaekers

Reputation: 58491

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

Upvotes: 4

Related Questions