CODEWITHSUNDEEP
mysqlgroup-bysumdistinct
EdwardC
EdwardC

Reputation: 105

Group by a summed variable

SELECT deposit.numberSuccessfulDeposits, count(distinct userid) 
FROM deposit WHERE deposit.asOfDate between '2016-04-01 00:00:00' and '2016-04-03 23:59:59'
AND deposit.licenseeId = 1306
GROUP BY deposit.numberSuccessfulDeposits

Sample output

numberSuccessfulDeposits     count(distinct userid) 
 0   228 
 1   878 
 2   90 
 3   37 
 4   17

However, if Bob made 1 deposit on Monday and 3 deposits on Tuesday, then it will count towards both "1" and "3" for number of successful deposits. 

numberSuccessfulDeposits     count(distinct userid) 
 0   ## 
 1   1 
 2   ## 
 3   1 
 4   ##

Ideally, it should only count towards "4"

numberSuccessfulDeposits     count(distinct userid) 
 0   ## 
 1   ## 
 2   ## 
 3   ## 
 4   1

Thoughts?

Upvotes: 0

Views: 33

Answers (2)

Kamil Gosciminski
Kamil Gosciminski

Reputation: 17147

Change the grouping to user-based and sum all occurences of deposits. Then count users for each sum of those deposits:

SELECT
  numberSuccessfulDeposits,
  COUNT(userid) AS users_count
FROM (
  SELECT 
    sum(numberSuccessfulDeposits) AS numberSuccessfulDeposits, 
    userid
  FROM deposit
  WHERE asOfDate between '2016-04-01 00:00:00' and '2016-04-03 23:59:59'
    AND licenseeId = 1306
  GROUP BY userid
  ) t
GROUP BY numberSuccessfulDeposits

Edit: Grouping deposints into 0, 1, 2, 3+ category would look like that:

SELECT
  numberSuccessfulDeposits,
  COUNT(userid) AS user_count
FROM (
  SELECT
    CASE WHEN numberSuccessfulDeposits >= 3 THEN '3+' ELSE numberSuccessfulDeposits::TEXT END AS numberSuccessfulDeposits,
    userid
  FROM (
    SELECT 
      sum(numberSuccessfulDeposits) AS numberSuccessfulDeposits, 
      userid
    FROM deposit
    WHERE asOfDate between '2016-04-01 00:00:00' and '2016-04-03 23:59:59'
      AND licenseeId = 1306
    GROUP BY userid
    ) t
  ) f
GROUP BY numberSuccessfulDeposits

Upvotes: 1

Barmar
Barmar

Reputation: 780909

Calculate the per-user sum in a subquery, then the per-total count in the main query.

SELECT totalDeposits, COUNT(*)
FROM (SELECT userid, SUM(numberOfSuccessfulDeposits) AS totalDeposits
      FROM deposit 
      WHERE deposit.asOfDate between '2016-04-01 00:00:00' and '2016-04-03 23:59:59'
      AND deposit.licenseeId = 1306
      GROUP BY userid) AS subquery
GROUP BY totalDeposits

Upvotes: 0

Related Questions