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

    Unanswered: SELECT, SORT, and SUM Query

    Hello everyone,

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

    Illustration data as follow

    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')

    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
    What did you tried earlier?
    You should make use of Sum, Group by
    Here is one of the possible solutions

    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)

    Failing to plan is Planning to fail

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

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

  4. #4
    Join Date
    Feb 2004
    Burbank & Santa Cruz de la Sierra

    ...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
    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 "sqlblindman"

Posting Permissions

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