Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2005
    Posts
    2

    Unanswered: SELECT, SORT, and SUM Query

    Hello everyone,

    I Have a table with columns:
    - CustomerID
    - DateTransactionCompleted
    - AmountPaid

    Illustration data as follow

    GO
    INSERT INTO Cus_Tab_Dev_2 VALUES(30,'12-12-2004','18000.00')
    INSERT INTO Cus_Tab_Dev_2 VALUES(30,'12-15-2004','2000.00')
    INSERT INTO CUS_Tab_Dev_2 VALUES(30,'1-16-2005','15000.00')
    INSERT INTO CUS_TAB_DEV_2 VALUES(42,'2-2-2005','12000.00')
    GO

    What I want is a report that tells me the Total Sales per month Per Customer in the following manner:

    Customer ID Month/Year Total Per Month
    ----------- ----------- ----------------
    30 12-2004 20000.00
    30 1-2005 15000.00
    42 2-2005 12000.00

    This is a homework, and I have been trying to get it to work since this morning. Any help is appreciated.

    PS: If you do not feel you want/should help. Please don't call me names! I don't ask people to help me with my homework usually. But, I am running out of time on the assignment.

  2. #2
    Join Date
    Oct 2003
    Posts
    357
    What did you tried earlier?
    You should make use of Sum, Group by
    Here is one of the possible solutions

    Code:
    Declare @Cus_Tab_Dev_2 table(CustomerID int,DateTransactionCompleted datetime,AmountPaid float)
    INSERT INTO @Cus_Tab_Dev_2 VALUES(30,'12-12-2004','18000.00')
    INSERT INTO @Cus_Tab_Dev_2 VALUES(30,'12-15-2004','2000.00')
    INSERT INTO @CUS_Tab_Dev_2 VALUES(30,'1-16-2005','15000.00')
    INSERT INTO @CUS_TAB_DEV_2 VALUES(42,'2-2-2005','12000.00')
    Select CustomerID,cast(month(DateTransactionCompleted) as varchar)+'-'+cast(year(DateTransactionCompleted) as varchar),
    sum(AmountPaid) from @Cus_Tab_Dev_2 group by CustomerID,
    cast(month(DateTransactionCompleted) as varchar)+'-'+cast(year(DateTransactionCompleted) as varchar)
    Madhivanan

    Failing to plan is Planning to fail

  3. #3
    Join Date
    Oct 2005
    Posts
    2
    Thanks a million, worked beautifully.

    I also learned that GROUP BY means:: GROUP BY! Thanks again.

  4. #4
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    *sigh*

    ...a teeny-tiny part of the world just died
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I have no problem helping with homework IF the poster shows that they have made an attempt to solve it themselves. But this person did not post any code that he had already tried.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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