SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 SQL 2008 group by and sum
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mjimenezh
Yak Posting Veteran

Mexico
63 Posts

Posted - 11/01/2013 :  16:08:44  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 11/01/2013 :  16:16:28  Show Profile  Reply with Quote
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
Go to Top of Page

bitsmed
Constraint Violating Yak Guru

421 Posts

Posted - 11/01/2013 :  16:17:14  Show Profile  Reply with Quote
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.
Go to Top of Page

mjimenezh
Yak Posting Veteran

Mexico
63 Posts

Posted - 11/01/2013 :  16:43:17  Show Profile  Reply with Quote
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.
Go to Top of Page

bitsmed
Constraint Violating Yak Guru

421 Posts

Posted - 11/01/2013 :  17:03:17  Show Profile  Reply with Quote
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)
Go to Top of Page

mjimenezh
Yak Posting Veteran

Mexico
63 Posts

Posted - 11/01/2013 :  17:38:51  Show Profile  Reply with Quote
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
Go to Top of Page

bitsmed
Constraint Violating Yak Guru

421 Posts

Posted - 11/01/2013 :  18:05:25  Show Profile  Reply with Quote
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)
Go to Top of Page

mjimenezh
Yak Posting Veteran

Mexico
63 Posts

Posted - 11/01/2013 :  18:10:36  Show Profile  Reply with Quote
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.
Go to Top of Page

mjimenezh
Yak Posting Veteran

Mexico
63 Posts

Posted - 11/04/2013 :  11:16:08  Show Profile  Reply with Quote
Thank you some much guys, it's working perfect now with the bitsmed recommendation.


Best Regards..
Go to Top of Page

mjimenezh
Yak Posting Veteran

Mexico
63 Posts

Posted - 11/04/2013 :  16:33:03  Show Profile  Reply with Quote
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.

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000