Reputation: 17

SQL return having sum value

I have a query that checks a group and makes sure that it has more than 1 value under 2

SELECT `tile` FROM TFResults
GROUP BY `tile`
HAVING SUM(CASE WHEN `Place` < 2 THEN 1 ELSE 0 END)> 1 ;

I would like to return the value of sum also but can't seem to get it to work

SELECT `tile`, thesum
FROM TFResults
GROUP BY `tile`
HAVING SUM(CASE WHEN `Place` < 2 THEN 1 ELSE 0 END) as thesum > 1 ;

Upvotes: 0

Answers (3)

Reputation: 4420

First you need to move sum part to select statement. And if you need only one column to check then use if instead CASE. Check Below

SELECT tile, SUM(if(Place < 2, 1, 0)) place_sum 
FROM TFResults
GROUP BY tile
HAVING place_sum > 1

Upvotes: 0

Reputation: 12378

Just move sum statement to select:

SELECT
    `tile`,
    SUM(CASE WHEN `Place` < 2 THEN 1 ELSE 0 END) AS thesum 
FROM TFResults
GROUP BY `tile`
HAVING thesum > 1 ;

Upvotes: 0

Reputation: 204766

You define alias names in the select clause

SELECT tile, 
       SUM(CASE WHEN Place < 2 THEN 1 ELSE 0 END) as thesum
FROM TFResults
GROUP BY tile
HAVING thesum > 1

Upvotes: 1