mysql - SQL SUM Query Modifcation -

mysql - SQL SUM Query Modifcation -

i have sum sql query beingness used display chart in asp.net.

it sums amount of quantities have same value example:

quantity|total --------|----- 1 | 5 2 | 10 3 | 12 4 | 16 5 | 5 6 | 8

basicaly, there 5 orders quantity = 1, there 10 orders quantity = 2 , on.

select quantity, sum(quantity) total orders grouping quantity

thats code that.

i wanna know how can create query result.

quantity|total --------|----- 1 | 5 2 | 10 3 | 12 4 | 16 5 | 5 6 | 8 6+ | 5

so every order has quantity on 6, should added , displayed 6+ | 5.

any help?

you utilize case statement in select , group by:

select case when quantity > 6 '6+' else cast(quantity char(2)) end quantity, sum(quantity) total orders grouping case when quantity > 6 '6+' else cast(quantity char(2)) end;

example on sql fiddle

n.b. based in statement:

basicaly, there 5 orders quantity = 1, there 10 orders quantity = 2 , on.

and expected results, sounds want utilize count instead of sum, since if there 10 orders quantity = 2, sum(quantity) yield 20, not 10 have indicated in results.

revised illustration on sql fiddle

mysql sql sum