Help Group by - 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

Help Group by

Author

Topic

mjimenezh
Yak Posting Veteran

81 Posts

Posted - 2013-12-26 : 16:34:38

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

VeeranjaneyuluAnnapureddy
Posting Yak Master

169 Posts

Posted - 2013-12-27 : 02:02:29

Hi,

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

veeranjaneyulu

cgraus
Starting Member

12 Posts

Posted - 2013-12-27 : 04:53:16

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.

mjimenezh
Yak Posting Veteran

81 Posts

Posted - 2013-12-27 : 11:39:08

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)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-28 : 01:44:29

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
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources