CODEWITHSUNDEEP
mysqlcountunion
randomKek
randomKek

Reputation: 1128

same table count union

SELECT COUNT(*) as totalHappenings FROM `happenings` WHERE `userId` = ? 
UNION 
SELECT COUNT(*) as xHappenings FROM `happenings` WHERE `userId` = ? AND `destinationObjectType` = \'2\' 
UNION 
SELECT COUNT(*) as yHappenings FROM `happenings` WHERE `userId` = ? AND `destinationObjectType` = \'1\'

Since it's the same table, and I don't wanna pass through 3 times the userId parameter how can I solve this the best way?

Upvotes: 0

Views: 443

Answers (3)

Cylindric
Cylindric

Reputation: 5894

SELECT 
    COUNT(*) AS totalHappenings,
    SUM(CASE WHEN `destinationObjectType` = \'2\' THEN 1 ELSE 0 END) AS xHappenings, 
    SUM(CASE WHEN `destinationObjectType` = \'1\' THEN 1 ELSE 0 END) AS yHappenings 
FROM `happendings` 
WHERE `userId` = ?

Result:

totalHappenings xHappenings yHappenings
      24            10          14

Upvotes: 2

rabudde
rabudde

Reputation: 7722

try the shortest way:

SELECT COUNT(*) as totalHappenings, SUM(`destinationObjectType` = \'2\') AS xHappenings, SUM(`destinationObjectType` = \'1\') AS yHappenings FROM `happenings` WHERE `userId` = ?

comparision inside SUM returns true or false (1 or 0) so there is no need for IF or CASE statements

Upvotes: 0

melihcelik
melihcelik

Reputation: 4599

You can do this with if statements inside select clause:

  SELECT
    COUNT(userId) as totalHappenings, 
    SUM(IF(`destinationObjectType`='2',1,0) as xHappenings,
    SUM(IF(`destinationObjectType`='1',1,0) as yHappenings
  FROM `happenings`
  WHERE `userId` = ?

This will surely return your results in 3 columns. Your original query was returning in 3 rows but I think that is not a problem.

Upvotes: 1

Related Questions