Comparing two values in same column

Question

I have a table named login with the fields id and success (0 or 1).

I need to return only the ids where there are more entries with success at 1 than 0 (for each id).

So far I have only been able to make a query for #success, another for #hits and another that compares if #hits > #fail

/*returns: id, #hits */
SELECT id, count(*) AS hits  
FROM login u 
WHERE u.success = 1 
GROUP BY id;

/*returns: id, #fails*/
SELECT id, count(*)  AS fails
FROM login u 
WHERE u.success = 0 
GROUP BY id;

/*returns id, #hits, #fails, #hits > #fails? */
SELECT id, SUM(success = 1) AS 'Hits' , SUM(success = 0) AS 'Fails', SUM(success = 1) > SUM(success = 0) as 'hits > fails?'
FROM login u
GROUP BY id;

Having just one field, I can't make any direct comparison.

Is there any way to compare values from query 1 and query 2?

Can I change query 3 to just return where SUM(success = 1) > SUM(success = 0)?

Thank you in advance.

thepiyush13 · Accepted Answer · 2015-12-03 23:12:25Z

1

Not sure but you can try something like this :

SELECT col
  FROM tbl
 GROUP BY col
 HAVING SUM(col1) > SUM(col2)

answered Dec 3, 2015 at 23:12

thepiyush13

1,3211 gold badge8 silver badges9 bronze badges

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

1 Comment

Pedro Over a year ago

HAVING SUM(success = 1) > SUM(success = 0) worked, thanks

Collectives™ on Stack Overflow

Comparing two values in same column

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related