CODEWITHSUNDEEP
sqloracle-database
Heisenberg
Heisenberg

Reputation: 5299

How to sum and count in grouping in sql

I have tables like below. I would like to grouping and counting by referreing to its score.

customer   score
A      10
A      20
B      30
B      40
C      50
C      60

First, I would like to sum score by customer It achived by group by method.

customer score
A        30
B        70
C        110

second, I would like to count by binning following band. I couldn't figure out how to count after grouping

band    count
0-50     1
50-100   1
100-     0

Are there any way to achieve this?

Thanks

Upvotes: 0

Views: 103

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1271013

I would recommend two levels of aggregation but with a left join:

select b.band, count(c.customer)
from (select 0 as lo, 50 as hi, '0-50' as band from dual union all
      select 50 as lo, 100 as hi, '50-100' as band from dual union all
      select 100 as lo, null as hi, '100+' as band from dual
     ) b left join
     (select customer, sum(score) as score
      from myTable
      group by customer
     ) c
     on c.score >= b.lo and
        (c.score < b.hi or b.hi is null)
group by b.band;

This structure also suggests that the bands can be stored in a separate reference table. That can be quite handy, if you intend to reuse these across different queries or over time.

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522741

You could use a union approach:

WITH cte AS (
    SELECT COUNT(*) AS score
    FROM yourTable
    GROUP BY customer
)

SELECT '0-50' AS band, COUNT(*) AS count, 0 AS position FROM cte WHERE score <= 50 UNION ALL
SELECT '50-100', COUNT(*), 1 FROM cte WHERE score > 50 AND score <= 100 UNION ALL
SELECT '100-', COUNT(*), 2 FROM cte WHERE score > 100
ORDER BY position;

Upvotes: 2

zealous
zealous

Reputation: 7503

Try the following with case expression

select
  case
    when score >= 0 and score <= 50 then '0-50'
    when score >= 50 and score <= 100 then '50-100'
    when score >= 100  then '100-'
  end as band,
  count(*) as count
from
(
  select
    customer,
    sum(score) as score
  from myTable
  group by
    customer
) val
group by
  case
    when score >= 0 and score <= 50 then '0-50'
    when score >= 50 and score <= 100 then '50-100'
    when score >= 100  then '100-'
  end

Upvotes: 1

Related Questions