MajorGeeks Support Forums

Go Back   MajorGeeks Support Forums > ----------= PC, Desktop and Laptop Support =---------- > Software
Register FAQ Members List Calendar Casino Mark Forums Read

Software Software such as operating systems like Windows XP, Windows Vista, Windows 7 etc., or specific programs.


Reply
 
Thread Tools Display Modes
  #1  
Old 05-07-12, 16:26
jcast jcast is offline
Private E-2
 
Join Date: Apr 2012
Posts: 17
Thanks: 2
Thanked 0 Times in 0 Posts
Default Sql server group by

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
Reply With Quote
Sponsored links
  #2  
Old 05-08-12, 12:27
jcast jcast is offline
Private E-2
 
Join Date: Apr 2012
Posts: 17
Thanks: 2
Thanked 0 Times in 0 Posts
Default Re: Sql server group by

I already figured it out.

Thanks
Reply With Quote
Reply

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Facebook Group Major Attitude The Lounge 185 03-18-12 08:49
Win Server 2003 Security/group question... ifrydr Software 1 01-09-12 13:37
Mod Group Name. Spock96 The Lounge 20 10-23-11 00:50
Group Policy blacknwhite88 Software 0 10-14-10 01:19
Group Policies mrudella Software 3 04-07-04 19:40


All times are GMT -5. The time now is 16:54.

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


All content Copyright MajorGeeks.com source code Powered by vBulletin® Version 3.8.4
Copyright © 2009 vBulletin Solutions, Inc. All rights reserved.
Ad Management by RedTyger