CODEWITHSUNDEEP
phpsqldatabaselaravellaravel-5.4
Peyman.H
Peyman.H

Reputation: 1952

select count and sum of data for different ranges of another column

I have a table with the design below:

SaleDataTbl :

ColumnA has different values and i want to group them in 4 ranges: [0-100) , [100-200), [200-300), [300-400] and I want the count of rows for each ranges mentioned, and sum of ColumnB and ColumnC for each ranges. I can do it by multiple queries but Can i do it with only one query?

Thanks in advance

Upvotes: 2

Views: 81

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271091

This is not much more than an aggregation query:

select grp, count(*), sum(columnb), sum(columnc)
from (select t.*,
             (case when columnA < 100 then '[000-100)'
                   when columnA < 200 then '[100-200)'
                   when columnA < 300 then '[200-300)'
                   when columnA < 400 then '[300-400)'
              end) as grp
      from t
     ) t
group by grp
order by grp;

Notes:

  • I doubt you really want inclusive ranges. Why would "100" show up in two buckets?
  • The subquery is not really necessary. It highlights the definition of the ranges.
  • I don't really see a natural way to split this into multiple queries.

Upvotes: 2

Related Questions