SQL Count with multiple conditions then join

Question

Quick one,

I have a table, with the following structure

id  lid  taken
1   1    0
1   1    0
1   1    1
1   1    1
1   2    1

Pretty simply so far right?

I need to query the taken/available from the lid of 1, which should return

taken  available
2      2

I know I can simply do two counts and join them, but is there a more proficient way of doing this rather than two separate queries?

I was looking at the following type of format, but I can not for the life of me get it executed in SQL...

SELECT
   COUNT(case taken=1) AS taken, 
   COUNT(case taken=0) AS available FROM table
WHERE 
   lid=1

Thank you SO much.

cdhowie · Accepted Answer · 2012-06-13 15:23:11Z

3

You can do this:

SELECT taken, COUNT(*) AS count
FROM table
WHERE lid = 1
GROUP BY taken

This will return two rows:

taken  count
0      2
1      2

Each count corresponds to how many times that particular taken value was seen.

answered Jun 13, 2012 at 15:23

cdhowie

172k25 gold badges303 silver badges324 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jonathan Over a year ago

Different way all together, great SQL skills :)

bendataclear · Accepted Answer · 2012-06-13 15:24:11Z

2

Your query is correct just needs juggling a bit:

SELECT
   SUM(case taken WHEN 1 THEN 1 ELSE 0 END) AS taken, 
   SUM(case taken WHEN 1 THEN 0 ELSE 1 END) AS available FROM table
WHERE 
   lid=1

Alternatively you could do:

SELECT
   SUM(taken) AS taken, 
   COUNT(id) - SUM(taken) AS available 
FROM table
WHERE 
   lid=1

answered Jun 13, 2012 at 15:24

bendataclear

3,8574 gold badges34 silver badges58 bronze badges

3 Comments

Matt Smucker Over a year ago

Your top query returns 4 and 4, you're just counting the rows each time

bendataclear Over a year ago

Just spotted that thanks, meant to be SUM not COUNT for first example :)

Jonathan Over a year ago

That second query just made me punch myself in the throat. I guess I kind of forgot about SUM.. Thanks!

Matt Smucker · Accepted Answer · 2012-06-13 15:24:46Z

1

SELECT
   SUM(case WHEN taken=1 THEN 1 ELSE 0 END) AS taken, 
   SUM(case WHEN taken=0 THEN 1 ELSE 0 END) AS available 
FROM table
WHERE lid=1

answered Jun 13, 2012 at 15:24

Matt Smucker

5,2442 gold badges24 silver badges36 bronze badges

Comments

wildplasser · Accepted Answer · 2012-06-13 15:35:37Z

1

Weird application of CTE's:

WITH lid AS (
        SELECT DISTINCT lid FROM taken
        )
, tak AS (
        SELECT lid,taken , COUNT(*) AS cnt
        FROM taken t0
        GROUP BY lid,taken
        )
SELECT l.lid
, COALESCE(a0.cnt, 0) AS available
, COALESCE(a1.cnt, 0) AS taken
FROM lid l
LEFT JOIN tak a0 ON a0.lid=l.lid AND a0.taken = 0
LEFT JOIN tak a1 ON a1.lid=l.lid AND a1.taken = 1
WHERE l.lid=1
        ;

answered Jun 13, 2012 at 15:35

wildplasser

44.5k9 gold badges72 silver badges116 bronze badges

1 Comment

wildplasser Over a year ago

To the downvoter: please explain. The query works and gives the expected result. It might differ from what you expect, but that is no reason to reject it.

Collectives™ on Stack Overflow

SQL Count with multiple conditions then join

4 Answers 4

1 Comment

3 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

3 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related