CODEWITHSUNDEEP
sqlpresto
javacash
javacash

Reputation: 173

Sum Over Certain Grouped Rows

I have a data set that shows the number of guests and revenue for each category of consumer (consumer that has visited once, twice, 3 times, 4 times, 5 times, etc).

It looks like this: enter image description here

My query to get that table above was:

SELECT number_of_trans, COUNT(*) AS guest_count, SUM(amount) AS revenue FROM customer_counting GROUP BY number_of_trans ORDER BY number_of_trans

Now, I want to sum both guest_count and revenue and I want to sum them for the following categories: 1,2,3,4,5-7,and 8+.

So really 1,2,3, and 4 would remain the same but I would be summing within rows 5 - 7 and rows from row 8 and onwards.

How would I do this? The table I want is below (don't worry about the accuracy of the values):

enter image description here

I hope my question is understood.

Upvotes: 1

Views: 48

Answers (2)

Caius Jard
Caius Jard

Reputation: 74605

Simpler form of Gordon's proposal:

SELECT 
  CASE WHEN number_of_trans < 5 THEN cast(number_of_trans as varchar)
    WHEN number_of_trans < 8 THEN '5-7'
    ELSE '8+' 
  END as freq_band, 
  COUNT(*) AS guest_count, 
  SUM(amount) AS revenue 
FROM customer_counting 
GROUP BY CASE WHEN number_of_trans < 5 THEN cast(number_of_trans as varchar)
    WHEN number_of_trans < 8 THEN '5-7'
    ELSE '8+' 
  END
ORDER BY freq_band

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269773

Use case and a subquery (or CTE):

SELECT (CASE WHEN number_of_trans < 5 THEN CAST(number_of_trans as varchar)
             WHEN number_of_trans < 8 THEN '5-7'
             ELSE '8+'
        END) as grp,
       SUM(guest_count) as guest_count, SUM(revenue) as revenue
FROM (SELECT number_of_trans, COUNT(*) AS guest_count, SUM(amount) AS revenue
      FROM customer_counting
      GROUP BY number_of_trans
     ) x
GROUP BY grp
ORDER BY MIN(number_of_trans);

Upvotes: 1

Related Questions