SQL 2008 group by and sum - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL 2008 group by and sum

Author

Topic

mjimenezh
Yak Posting Veteran

81 Posts

Posted - 2013-11-01 : 16:08:44

Hi to all, i'm learning sql code and I have a problem because I need this and I don't know how can I do it :

I have a sql 2008/r2 view that give me the billing history, I need to group by customer (Debtor_Number), if the customer has some bills (InvoiceNumber) I just need to show one row for every customer customer with the sum of the totals (InvoiceAmount) of every bill I don't know if i'm clear, this is the code I have :

SELECT DebtorNumber, cmp_name, TransactionTypeDescription, InvoiceNumber, InvoiceDate, OrigInvAmtTC, InvoiceAmountTC, TCCode, OrigInvAmt, InvoiceAmount, 
                      ReceiptPaid, PaymentDate, Balance, PaymentCondition, BaseAmount, TaxAmount, ExchangeRate, COGS, COSTSTD, cmp_acc_man, fullname, DueDate
FROM         dbo.View_BILPAID

Thanks for your help.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-11-01 : 16:16:28

I'm not 100% that I follow. Here is a guess. if that doesn't help, please see the links below.

SELECT 
	Debtor_Number,
	SUM(InvoiceAmount) AS SumInvoiceAmount
FROM
	dbo.View_BILPAID
GROUP BY
	Debtor_Number

http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2013-11-01 : 16:17:14

Mayby this


select debitornumber
      ,invoicenumber
      ,sum(invoiceamount)
  from dbo.view_bilpaid
 group by debitornumber
         ,invoicenumber

This will give you sum per debitor/invoice.
If you want sum per debitor, delete line with red.

mjimenezh
Yak Posting Veteran

81 Posts

Posted - 2013-11-01 : 16:43:17

Hi Guys, thanks for your help it works, but I looking that I must filter the InvvoiceDate first because the code sum of 2012 and 2013, I need to filter by date (InvoiceDate)between date1 and date2 or if this is not possible then the sum of every month, is this possible?

Thanks again for your help.

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2013-11-01 : 17:03:17

quote:
Originally posted by mjimenezh

Hi Guys, thanks for your help it works, but I looking that I must filter the InvvoiceDate first because the code sum of 2012 and 2013, I need to filter by date (InvoiceDate)between date1 and date2 or if this is not possible then the sum of every month, is this possible?

Thanks again for your help.

Try this


select debitornumber
      ,year(invoicedate) as theyear
      ,sum(invoiceamount)
  from dbo.view_bilpaid
 where invoicedate>='20120101'
   and invoicedate<'20140101'
 group by debitornumber
         ,year(invoicedate)

mjimenezh
Yak Posting Veteran

81 Posts

Posted - 2013-11-01 : 17:38:51

Thanks bistmed, the code is ok but it doesn't group the sum of the invoices'amount, if the debtor has 3 bills in the year shows 3 rows and I need just one row with the sum of the 3 bills, what I'm doing wrong?

SELECT 
	DebtorNumber,
	SUM(InvoiceAmount) AS SumInvoiceAmount,Year(InvoiceDate)AS TheYear,Month(InvoiceDate)AS TheMonth
FROM dbo.View_BILPAID
WHERE     (YEAR(InvoiceDate) = '2013') AND (MONTH(InvoiceDate) = '10')
GROUP BY
	DebtorNumber

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2013-11-01 : 18:05:25

quote:
Originally posted by mjimenezh

Thanks bistmed, the code is ok but it doesn't group the sum of the invoices'amount, if the debtor has 3 bills in the year shows 3 rows and I need just one row with the sum of the 3 bills, what I'm doing wrong?

SELECT
DebtorNumber,
SUM(InvoiceAmount) AS SumInvoiceAmount~~,Year(InvoiceDate)AS TheYear,Month(InvoiceDate)AS TheMonth~~
FROM dbo.View_BILPAID
WHERE (YEAR(InvoiceDate) = '2013') AND (MONTH(InvoiceDate) = '10')
GROUP BY
DebtorNumber

Above correction will show one row per debitor.
My previous suggestion, showed you one row per debitor/year.
If you want one row per debitor/year/month, try this


select debitornumber
      ,year(invoicedate) as theyear
      ,month(invoicedate) as themonth
      ,sum(invoiceamount)
  from dbo.view_bilpaid
 where invoicedate>='20120101'
   and invoicedate<'20140101'
 group by debitornumber
         ,year(invoicedate)
         ,month(invoicedate)

mjimenezh
Yak Posting Veteran

81 Posts

Posted - 2013-11-01 : 18:10:36

You're right, it was my error

, I didn't type the Year(InvoiceDate) into the Group Clause, it's working perfect.

Thanks a lot for your help.

mjimenezh
Yak Posting Veteran

81 Posts

Posted - 2013-11-04 : 11:16:08

Thank you some much guys, it's working perfect now with the bitsmed recommendation.

Best Regards..

mjimenezh
Yak Posting Veteran

81 Posts

Posted - 2013-11-04 : 16:33:03

Hi, I found another big detail, the currency, some bills are in usd and others are in mxn how can I make 2 sums, one with the sum of usd and other with the mxn bills?

quote:
Originally posted by mjimenezh

You're right, it was my error , I didn't type the Year(InvoiceDate) into the Group Clause, it's working perfect.

Thanks a lot for your help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources