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
 Help Group by
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mjimenezh
Yak Posting Veteran

Mexico
61 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

VeeranjaneyuluAnnapureddy
Posting Yak Master

India
164 Posts

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

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

veeranjaneyulu
Go to Top of Page

cgraus
Starting Member

Australia
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

mjimenezh
Yak Posting Veteran

Mexico
61 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

visakh16
Very Important crosS Applying yaK Herder

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