1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

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
     

Share This Page

MajorGeeks.Com Menu

MajorGeeks.Com \ 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 \ NEW! PC Games \ System Tools \ Macintosh \ Demonews.Com \ Top Downloads

MajorGeeks.Com \ News (Tech) \ Off Base (Other Websites News) \ Way Off Base (Offbeat Stories and Pics)

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