SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    858
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Need help with count(distinct)

    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!

    Screen Shot 2012-03-12 at Mar 12, 2012 2.18.58 PM.jpg


    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;
    Convert your dollars into silver coins. www.convert2silver.com

  2. #2
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,707
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  3. #3
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    858
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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?
    Convert your dollars into silver coins. www.convert2silver.com

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    i'm just taking a stab (ouch!!) in the dark...
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    858
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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
    Convert your dollars into silver coins. www.convert2silver.com

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Code:
    CONCAT('$',FORMAT(Silvers * 5 + Golds * 15 , 2)) AS Combined
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    858
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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?

    Thanks!
    Convert your dollars into silver coins. www.convert2silver.com

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    nope, i'm stuck too
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    858
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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.

    Thanks!
    Convert your dollars into silver coins. www.convert2silver.com


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •