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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Help Group by
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Yak Posting Veteran

81 Posts

Posted - 12/26/2013 :  16:34:38  Show Profile  Reply with Quote
Hi, I'm learning sql 2008/r2 code and I' lost with this :

I have a Sql view that shows me billing information and I need to group by BillNumber and Xrate (USD and MXN) and TaxCode (116,130,140) and I don't know how can I do, please help me, this is the code :

SELECT     faknr AS BillNumber, fakdat AS BillDate, artcode AS ItemCode, oms45 AS ItemDescription, esr_aantal AS ItemQuantity, prijs_n AS PriceNet, vvp AS StandardCost, 
                      pr_bedr AS AmountWithTax, ordernr AS OrderNumber, verzdebnr AS DebotNumber, koers AS XRate, bdr_ev_ed_val AS AmountNet, bdr_vat_val AS TaxPercent, 
                      docnumber AS DocumentID, res_id AS SalesRep, serial_number AS LotNumber, btw_code AS TaxCode1, TaxCode2, TaxCode3, TaxBasis2, TaxBasis3, TaxAmount1, 
                      TaxAmount2, TaxAmount3, syscreated, syscreator
FROM         dbo.frhsrg
WHERE     (TaxAmount2 > 0)
GROUP BY faknr, fakdat, artcode, oms45, esr_aantal, prijs_n, vvp, pr_bedr, ordernr, verzdebnr, koers, bdr_ev_ed_val, bdr_vat_val, docnumber, res_id, serial_number, 
                      btw_code, TaxCode2, TaxCode3, TaxBasis2, TaxBasis3, TaxAmount1, TaxAmount2, TaxAmount3, syscreated, syscreator

Posting Yak Master

169 Posts

Posted - 12/27/2013 :  02:02:29  Show Profile  Reply with Quote

Without using aggregate function ,Group by clause doesn't work.

Go to Top of Page

Starting Member

12 Posts

Posted - 12/27/2013 :  04:53:16  Show Profile  Reply with Quote
Group by only works if you have something to group. For example, if you were to select sum(faknr), this would add together all the values of your bill numbers. If you were to then group by fakdat, it would group the query by dates, and thus give you the totals for each date. If you don't have anything for a group to operate on, then there's no possible grouping. Do you mean you want to get a list of the other values for each group of bill number, Xrate and TaxCode ? There are ways of doing that, but the number of fields you have here, make them messy.

Remove the group by clause. Look at what you get. Then if it's not what you want, you can do two things:

1 - you can try 'DISTINCT' which will remove duplicate rows ( duplicate on all values, not just on the ones you're talking about )
2 - explain what values you're getting, and what sort of values you want to see ( that is, tell us the result set, and what the result set is that you want ) and then perhaps we can help more.
Go to Top of Page

Yak Posting Veteran

81 Posts

Posted - 12/27/2013 :  11:39:08  Show Profile  Reply with Quote
I have to group by faknr but considering the currency, every faknr can be on usd or mxn currency and can have different taxes like 116,130,140 I need to know the total amount of every tax and every currency but I don't found the way because the taxcode1 must be the 100 or 116 code and TaxCode2 can be the 130 or the 140 then how can I validate this to sum(taxamount1) and sum(taxamount2)

Edited by - mjimenezh on 12/27/2013 11:44:15
Go to Top of Page

Very Important crosS Applying yaK Herder

52326 Posts

Posted - 12/28/2013 :  01:44:29  Show Profile  Reply with Quote
I need to know the total amount of every tax and every currency

then you should be including currency and tax field also in group by i guess

SQL Server MVP
Go to Top of Page
  Previous Topic Topic Next 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.06 seconds. Powered By: Snitz Forums 2000