MySQL: Getting Count of Items that fulfill specific increments of a condition

Question

Consider a table showing various fruit and the quantity that they exist in:

x---------x-----x    
| FRUIT   | QTY |      
x---------x-----x
| Apple   |  4  |
| Orange  |  5  |
| Mango   |  4  |
| Grape   |  1  |
| Plum    |  2  |
| Peach   |  2  |
x---------x-----x

From this table I want to query the number of fruit (ie. count the number of records) that have a specific quantity starting from 0 and ending at MAX(QTY), so that my result set would be:

x-----x-------x    
| QTY | COUNT |      
x-----x-------x
|  0  |   0   |   //0 fruits have 0 quantity
|  1  |   1   |   //1 fruit (Grape) has 1 quantity
|  2  |   2   |   //2 fruits (Plum, Peach) have 2 quantity
|  3  |   0   |   //0 fruits have 3 quantity
|  4  |   2   |   //2 fruits (Apple, Mango) have 4 quantity
|  5  |   1   |   //1 fruit (Orange) has 5 quantity
x-----x-------x

How can this be achieved?

score 0 · Answer 1 · edited May 23 '17 at 12:13

You need to have a sequence table so that you can do left join with that table and give all values

Here is one way to do this

generation of numbers is taken from this post Generating a range of numbers in MySQL

select T1.SeqValue as Qty, isnull(T2.totalCount,0) as Count
from

(

 SELECT
    (TWO_1.SeqValue + TWO_2.SeqValue + TWO_4.SeqValue + TWO_8.SeqValue + TWO_16.SeqValue) SeqValue
FROM
    (SELECT 0 SeqValue UNION ALL SELECT 1 SeqValue) TWO_1
    CROSS JOIN (SELECT 0 SeqValue UNION ALL SELECT 2 SeqValue) TWO_2
    CROSS JOIN (SELECT 0 SeqValue UNION ALL SELECT 4 SeqValue) TWO_4
    CROSS JOIN (SELECT 0 SeqValue UNION ALL SELECT 8 SeqValue) TWO_8
    CROSS JOIN (SELECT 0 SeqValue UNION ALL SELECT 16 SeqValue) TWO_16 

)T1
left join
(
  select count(*) as totalCount, qty
  from table1
 group by qty
)T2
on T1.SeqValue = T2.qty

score 0 · Answer 2 · answered Dec 08 '14 at 05:24

0

select * from (select QTY,count(FRUIT) as Count,group_concat(FRUIT) as Fruit_Name from table group by QTY )t Order By QTY

answered Dec 08 '14 at 05:24

Ronak Shah

1,539
2
13
20

score 0 · Answer 3 · answered Dec 08 '14 at 06:07

Try This One :

Declare @sSQL as Varchar(1000), @sTemp as Varchar(4)
Declare @iLoop as int, @iMax as int
Set @iMax = (Select max(Qty) from table1)
Set @iLoop = 0
Set @sSQL = ''
While @iLoop <= @iMax
Begin
    Set @sTemp = (Select count(Qty) from table1 Where Qty = @iLoop Group By Qty)
    If @sTemp is Null
    Begin
        Set @sTemp = 0
    End
    Set @sSQL = @sSQL + ' Select  '+Cast(@iLoop as Varchar(4))+' as QTY,' + @sTemp+' as [COUNT] Union'

    Set @iLoop = @iLoop + 1
End
Set @sSQL = Left(@sSQL, Len(@sSQL)-5)
Exec(@sSQL)

MySQL: Getting Count of Items that fulfill specific increments of a condition

3 Answers3