CODEWITHSUNDEEP
mysql
Paul Stanley
Paul Stanley

Reputation: 4098

Group by under certain values

say I GROUP BY a COUNT(*).

COUNT(*)        Value
3               200
7               200
10              20
15              80
21              100
30              200
33              400
36              300
37              100

I want to sum every other count above 30 into its own row.

COUNT(*)        Value
3               200
7               200
10              20
15              80
21              100
30              200
30+             800

Is it possible to achieve this without a subquery/querying the data twice in mysql?

Thanks!

EDIT : When I meant subquery, I meant subquery in the sense that there wasn't 2 'full' queries being done, this:

SELECT * FROM(SELECT *...)

is fine.

Upvotes: 1

Views: 84

Answers (2)

Tom Cannaerts
Tom Cannaerts

Reputation: 648

You could use an IF() function on the result of the COUNT, but since that result is only available when the resultset is complete, that would require a subquery or at least a derived table.

SELECT t.somekey, SUM(IF(t.c > 30, 31, t.c)) As Cnt, SUM(t.s) As Value
FROM (
    SELECT somekey, COUNT(*) c, SUM(value) s
    FROM sometable
) as t
GROUP BY t.somekey

The result of this would be that everything larger than 30 would be grouped together as 31.

Upvotes: 0

John Woo
John Woo

Reputation: 263693

Try this,

SELECT  CASE WHEN Count > 30 THEN '30+' ELSE Count END `COUNT(*)`, 
        SUM(Value) Value
FROM    TableName
GROUP   BY CASE WHEN Count > 30 THEN '30+' ELSE Count END
ORDER   BY Count

OUTPUT

╔══════════╦═══════╗
║ COUNT(*) ║ VALUE ║
╠══════════╬═══════╣
║ 3        ║   200 ║
║ 7        ║   200 ║
║ 10       ║    20 ║
║ 15       ║    80 ║
║ 21       ║   100 ║
║ 30       ║   200 ║
║ 30+      ║   800 ║
╚══════════╩═══════╝

Upvotes: 2

Related Questions