Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    New Coder
    Join Date
    Aug 2005
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Question Help with Distinct and Count

    Hi,

    I have a table with a DATETIME field to log activity.

    I want to write a query that returns (up to) the most recent five unique months of activity, but that also shows my the total number of unique months so that if there are more than 5, I can show a 'more' button in my application.

    The query I started with is:

    Code:
    SELECT 
        DATE_FORMAT(activityDate, '%M, %Y') AS activityDate, COUNT(DATE_FORMAT(activityDate, '%M, %Y')) AS qty 
    FROM 
        activity
    WHERE 
        activityDate <= '2006-07-20 00:00:00' 
    GROUP BY 
        DATE_FORMAT(activityDate, '%M, %Y') 
    ORDER BY 
        activityDate DESC 
    LIMIT 
        5
    But this only shows the qty as the number of items within the month in that row.

    So then I tried:

    Code:
    SELECT 
        DATE_FORMAT(activityDate, '%M, %Y') AS activityDate, COUNT(*) AS qty 
    FROM 
        activity 
    WHERE 
        activityDate <= '2006-07-20 00:00:00' 
    GROUP BY 
        DATE_FORMAT(activityDate, '%M, %Y') 
    UNION 
    SELECT DISTINCT 
        'total' AS activityDate, COUNT(DATE_FORMAT(activityDate, '%M, %Y')) AS qty 
    FROM 
        activity 
    WHERE 
        activityDate <= '2006-07-20 00:00:00' 
    ORDER BY 
        activityDate DESC 
    LIMIT 
        6
    This gives me a row at the top of my recordset with 'total' in the 'activityDate' field, but the 'qty' field shows a total of all unique dates, not just 'month, year' (so for example, if I have three items in July '06, instead of counting July '06 as 1 it's adding 3 to the total, essentially ignoring my DISTINCT.)

    I'm sure I'm missing something extremely obvious and any help would be greatly appreciated.

    Thanks,
    C.

  • #2
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    DISTINCT does not work as a funciton, it works across the entire row of columns that you select.

    how about showing some sample data and sample output you are looking to return?

  • #3
    New Coder
    Join Date
    Aug 2005
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Question

    My 'activity' table may have the following

    Code:
    activityId     activityDate            userId
    ----------     -------------------     ------
    1              2006-01-01 10:01:00     1
    2              2006-02-01 14:45:00     2
    3              2006-03-01 16:57:00     3
    4              2006-04-01 15:17:00     1
    5              2006-05-01 07:03:00     2
    6              2006-06-01 19:06:00     3
    7              2006-07-01 20:10:00     1
    8              2006-07-02 15:22:00     2
    9              2006-07-03 14:38:00     3
    There are 7 unique months in there.

    I'd like the following details back (the 5 most recent months, plus a count of the total number of unique months so I know whether to show a 'more; button on my app.). Either:

    Code:
    activityDate     qty
    ------------     ---
    July, 2006       7
    June, 2006       7
    May, 2006        7
    April, 2006      7
    March, 2006      7
    Or

    Code:
    activityDate     qty
    ------------     ---
    total            7
    July, 2006       3
    June, 2006       1
    May, 2006        1
    April, 2006      1
    March, 2006      1
    Or a better suggestion

    Thanks in advance,
    C.


  •  

    Posting Permissions

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