Sql server group by

Discussion in 'Software' started by jcast, May 7, 2012.

  1. jcast

    jcast Private E-2

    Hi I trying to create a query that will count the invoice and group the results by Month and type

    I have this statement:

    select COUNT(*) As 'Count', DATEPART(YYYY, InvoiceDt) AS 'Year', DATEPART(MM, InvoiceDt) AS 'Month1', SUM(invoiceAmount)AS 'Invoice Amount',
    InvoiceType AS 'Type', InvoiceStatus AS 'Status'
    from Invoice
    where InvoiceStatus = 'C'
    group by InvoiceType, InvoiceStatus, InvoiceDt
    order by Month1

    and I am getting the following data:
    Count Year Month Amount Type Status
    1 2012 3 10.00 AccountCredit C
    1 2012 3 10.00 AccountCredit C
    1 2012 3 10.00 AccountCredit C
    1 2012 3 25.00 Recharge C
    1 2012 4 30.00 AccountCredit C
    1 2012 4 0.00 Activation C
    1 2012 4 0.00 Activation C
    1 2012 5 14.70 AccountCredit C
    1 2012 5 14.70 AccountCredit C
    1 2012 5 0.50 AccountCredit C
    1 2012 5 0.50 AccountCredit C

    But what I really need is to group by Type and month with the total count and the total Invoice amount, like this:

    Count Year Month Amount Type Status
    3 2012 3 30.00 AccountCredit C
    1 2012 3 25.00 Recharge C
    1 2012 4 30.00 AccountCredit C
    2 2012 4 0.00 Activation C
    4 2012 5 30.40 AccountCredit C

    and after that I need to join with another query that will give me the totals of each type, somethng like this:

    Null Null Null 90.40 AccountCredit C
    Null Null Null 25.00 Recharge C
    Null Null Null 0.00 Activation C

    Please help me to do this.

    Thanks
     
  2. jcast

    jcast Private E-2

    I already figured it out.

    Thanks
     

MajorGeeks.Com Menu

Downloads All In One Tweaks \ Android \ Anti-Malware \ Anti-Virus \ Appearance \ Backup \ Browsers \ CD\DVD\Blu-Ray \ Covert Ops \ Drive Utilities \ Drivers \ Graphics \ Internet Tools \ Multimedia \ Networking \ Office Tools \ PC Games \ System Tools \ Mac/Apple/Ipad Downloads

Other News: Top Downloads \ News (Tech) \ Off Base (Other Websites News) \ Way Off Base (Offbeat Stories and Pics)

Social: Facebook \ YouTube \ Twitter \ Tumblr \ Pintrest \ RSS Feeds