My query produces a row for every day of the month. Then it counts up all the transactions in the payments table to show me how many Silver, or Gold memberships were paid for each day of the month. The final column, “Combined” calculates in dollars how much revenue was collected based on Silver memberships being $5.00 and Gold memberships being $15.
The problem is that the query is adding up every payment ever made for each day of the month. So if a user named Joe has been in our program for 3 months, and he upgraded on the 1st of January, the query is counting that has three payments. What I’m really trying to find out is the number of UNIQUE payments for each day of the month. So Joe should only count as one unique payment for the 1st day of the month. Is this making sense? I am using this report to predict future payments based on how many silver or gold memberships have been paid on this same date during past months.
Thanks!
select dayofmonth(payDate) as Day,
count( case when type = ‘Silver’ and frequency = ‘Monthly’ then 1 else null end ) as Silvers,
count( case when type = ‘Gold’ and frequency = ‘Monthly’ then 1 else null end ) as Golds,
concat(‘$’,format(
count( case when type = ‘Silver’ and frequency = ‘Monthly’ then 1 else null end ) * 5
+
count( case when type = ‘Gold’ and frequency = ‘Monthly’ then 1 else null end ) * 15
,2)) as Combined
from payments
group by dayofmonth(payDate) order by Day;
you would have to show us the table with Joe in it for us to infer what the problem is there. From the screen shot you’ve attached it isn’t possible to tell why he is being counted more than once.
Let’s say Joe is the only user in my whole database and he upgraded to the Silver membership on January first. When I run my query, I want it to show that there is only 1 Silver for the 1st day of the month. Instead, my query is showing 3 Silvers, because Joe was charged on Jan 1st, Feb 1st and March 1st. Does this help?
SELECT theday
, Silvers
, Golds
, CONCAT('$',FORMAT(Silvers * 5 + Golds * 15 , 2) AS Combined
FROM ( SELECT theday
, SUM( CASE WHEN type = 'Silver' THEN day_type_count ELSE NULL END ) AS Silvers
, SUM( CASE WHEN type = 'Gold' THEN day_type_count ELSE NULL END ) AS Golds
FROM ( SELECT DAYOFMONTH(payDate) AS theday
, type
, COUNT(*) AS day_type_count
FROM payments
WHERE frequency = 'Monthly'
GROUP
BY DAYOFMONTH(payDate)
, type ) AS t1
GROUP
BY theday ) AS t2
ORDER
BY theday
Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘FROM’ at line 5
Rudy, your query produces the exact same numbers as mine, which are not correct. Let me see if I can explain it another way. By using the following membership upgrade examples:
Jan 1st = Bill upgrades to the silver account. Monthly recurring cost is $5.00
Feb 1st = Susan upgrades to the silver account. Monthly recurring cost is $5.00
Mar 1st = Alex upgrades to the silver account. Monthly recurring cost is $5.00
I need my query to show that for the 1st, there are 3 recurring subscriptions One for Bill, Susan and Alex. Since we are currently in the month of March, the query you and I made is giving a total of 6 silvers. 3 for Bill: Jan, Feb, Mar. 2 for Susan: Feb, Mar. 1 for Alex: Mar.
I’m baffled how to fix this problem (as usual). Can you take another stab?
Would it help to use distinct somewhere in the query? That way even if Bill has three payments made on the first of the month (Jan 1st, Feb 1st, Mar 1st), only one will be counted. We do have a user ID field to work with in the payments table. It is called uID.