How to sum and count in grouping in sql

Question

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

Tim Biegeleisen · Accepted Answer · 2020-06-08 09:32:21Z

2

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;

edited Jun 8, 2020 at 9:32

answered Jun 8, 2020 at 7:44

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Arun Palanisamy Over a year ago

Nice approach. I think, for the second select statement , the where condition should be score >= 50 and score <= 100?isn't it?

Tim Biegeleisen Over a year ago

@Arun Yes, you're right. Thanks for the correction.

zealous · Accepted Answer · 2020-06-08 07:43:44Z

1

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

answered Jun 8, 2020 at 7:43

zealous

7,5434 gold badges20 silver badges39 bronze badges

Comments

Gordon Linoff · Accepted Answer · 2020-06-08 11:44:03Z

1

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.

answered Jun 8, 2020 at 11:44

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Collectives™ on Stack Overflow

How to sum and count in grouping in sql

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related