CODEWITHSUNDEEP
mysqlsql
ashutosh
ashutosh

Reputation: 69

count product for different discount range

I have Products table with discount like

productid discount
1            12
2            22
3            32
4            45
5            55
6            57
7            63
8            72
9            85

i want sql query for product count where discount 10%-100% or discount 20%-100% or discount 30%-100% or discount 40%-100% so on .. discount range

My sql query is 

SELECT Count(product_id)                     AS product_count, 
       Substring_index(value_range, '-', 1)  AS start, 
       Substring_index(value_range, '-', -1) AS END 
FROM   (SELECT product_id, 
               discount, 
               CASE 
                 WHEN discount BETWEEN 80 AND 90 THEN '80-90' 
                 WHEN discount BETWEEN 70 AND 90 THEN '70-90' 
                 WHEN discount BETWEEN 60 AND 90 THEN '60-90' 
                 WHEN discount BETWEEN 50 AND 90 THEN '50-90' 
                 WHEN discount BETWEEN 40 AND 90 THEN '40-90' 
                 WHEN discount BETWEEN 30 AND 90 THEN '30-90' 
                 WHEN discount BETWEEN 20 AND 90 THEN '20-90' 
                 WHEN discount BETWEEN 10 AND 90 THEN '10-90' 
               END AS value_range 
        FROM   products) AS T2 
GROUP  BY value_range 
ORDER  BY Cast(start AS UNSIGNED) ASC

but it not giving desire result expected result is

discount_range countproduct
10%-100%            9
20%-100%            8
30%-100%            7
40%-100%             6..   so on

Here is My output.

enter image description here

Upvotes: 0

Views: 317

Answers (4)

Rajat
Rajat

Reputation: 5803

If you don't want to create a second table, try building off of this:

select "10%-100%" as discount_range, (select count(*) from products where discount between 10 and 100) as product_count
union
select "20%-100%" as discount_range, (select count(*) from products where discount between 20 and 100) as product_count
union
select "30%-100%" as discount_range, (select count(*) from products where discount between 30 and 100) as product_count
union
select "40%-100%" as discount_range, (select count(*) from products where discount between 40 and 100) as product_count

Upvotes: 0

Y.K.
Y.K.

Reputation: 692

try this one

select
    x.bin,
    x.bin_count,
    sum(y.bin_count) as cumulative_count
from
    (   select
            floor(discount / 10) as lower_bound,
            concat('[', floor(discount / 10) * 10, ' - ', floor(discount / 10) * 10 + 10, ')') as bin,
            count(*) as bin_count
        from
            t1
        group by
            1, 2) as x
    join (  select
                floor(discount / 10) as lower_bound,
                concat('[', floor(discount / 10) * 10, ' - ', floor(discount / 10) * 10 + 10, ')') as bin,
                count(*) as bin_count
            from
                t1
            group by
                1, 2) as y on x.lower_bound >= y.lower_bound
group by
    1, 2

one more

select
    x.bin,
    x.bin_count,
    concat('[', min(y.lower_bound), ' - ', max(y.upper_bound), ')') as cumulative_bin,
    sum(y.bin_count) as cumulative_count
from
    (   select
            floor(discount / 10) * 10 as lower_bound,
            floor(discount / 10) * 10 + 10 as upper_bound,
            concat('[', floor(discount / 10) * 10, ' - ', floor(discount / 10) * 10 + 10, ')') as bin,
            count(*) as bin_count
        from
            t1
        group by
            1, 2, 3) as x
    join (  select
                floor(discount / 10) * 10 as lower_bound,
                floor(discount / 10) * 10 + 10 as upper_bound,
                concat('[', floor(discount / 10) * 10, ' - ', floor(discount / 10) * 10 + 10, ')') as bin,
                count(*) as bin_count
            from
                t1
            group by
                1, 2, 3) as y on x.lower_bound >= y.lower_bound-- order by 1, 5
where
    x.lower_bound >= 10
    and y.lower_bound >= 10
group by
    x.bin

Upvotes: 0

forpas
forpas

Reputation: 164224

You need a query that counts the products inside the query that groups by range:

select 
  concat(t.floorvalue, '%-90%') discount_range,
  t.countproduct
from (  
  select
    floor(p.discount / 10) * 10 floorvalue,
    (select count(*) from products where discount >= floorvalue) countproduct
  from products p 
  group by floorvalue
) t 
order by discount_range

See the demo.
Results:

| discount_range | countproduct |
| -------------- | ------------ |
| 10%-90%        | 9            |
| 20%-90%        | 8            |
| 30%-90%        | 7            |
| 40%-90%        | 6            |
| 50%-90%        | 5            |
| 60%-90%        | 3            |
| 70%-90%        | 2            |
| 80%-90%        | 1            |

Upvotes: 0

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48207

First you need create a table for your ranges:

CREATE TABLE Ranges
    (`start` int, `end` int)
;

INSERT INTO Ranges
    (`start`, `end`)
VALUES
    (10, 100),
    (20, 100),
    (30, 100),
    (40, 100),
    (50, 100),
    (60, 100),
    (70, 100),
    (80, 100),
    (90, 100)
;

Then just found on what ranges each product discount is part of:

SELECT  `start`, `end`, `productid`, `discount`
FROM ranges
LEFT JOIN  products
  ON products.discount between `start` and `end`

Then just count it:

SQL DEMO

SELECT `start`, `end`, COUNT(`productid`)
FROM (
      SELECT  `start`, `end`, `productid`, `discount`
      FROM ranges
      LEFT JOIN  products
        ON products.discount between `start` and `end`
     ) t
GROUP BY `start`, `end`  
ORDER BY `start`

OUTPUT

| start | end | COUNT(`productid`) |
|-------|-----|--------------------|
|    10 | 100 |                  9 |
|    20 | 100 |                  8 |
|    30 | 100 |                  7 |
|    40 | 100 |                  6 |
|    50 | 100 |                  5 |
|    60 | 100 |                  3 |
|    70 | 100 |                  2 |
|    80 | 100 |                  1 |
|    90 | 100 |                  0 |

Upvotes: 1

Related Questions