Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2010
    Posts
    2

    Unanswered: Grouping by Month

    hi all, i am new to SQL and trying to pick up the pieces urgently

    any urgent help will be appreciated

    Where MEMBERS.ID links to SALES.MemberID write SQL queries to achieve an output dexcribed by the following

    2xtables

    MEMBERS
    ID int, not null
    FirstName text
    LastName text
    EmailAddress text

    SALES
    ID int, not null
    MemberID int
    PurchaseDate datetime
    Description text
    Quantity int
    Amount money

    and the question
    6) Return a sum of sales by month which can be matched to a member sale.

    thus far i have the query though its not what i am after
    SELECT SALES.MemberID, MEMBERS.FirstName, MEMBERS.LastName, Sum(SALES.Amount) AS SumOfAmount
    FROM MEMBERS INNER JOIN SALES ON MEMBERS.ID = SALES.MemberID
    GROUP BY SALES.MemberID, MEMBERS.FirstName, MEMBERS.LastName;

    which gives the total for each MemberID
    but i am assuming based on the question i am asked to separate these figures into related month columns

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    First attempt looks good and you are on the right track. So you have to pull the month out of the date column.

  3. #3
    Join Date
    Jun 2010
    Posts
    2
    sorry

    could you direct me a little bit further. sort of stuck

    been told to use the MONTH(PurchaseDate) in my query but lost on aything further than this.

    any direction would be great

  4. #4
    Join Date
    Jun 2010
    Posts
    4
    can try these queries,

    1)select m.firstname,m.lastname,month(s.purchasedate) as month_purchase,sum(s.Amount) as tot_amount from MEMBERS m join SALES s ON (m.ID=s.MemberID) group by m.ID,month_purchase

    2)select month(purchasedate) as month_purchase,sum(Amount) as tot_amount from SALES group by month_purchase

Posting Permissions

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