More Distinct - 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

More Distinct

Author

Topic

tranquilraven
Starting Member

19 Posts

Posted - 2012-06-11 : 12:15:19

I am trying to create a CSV file that lists each state only once as well as each zip code for the state only once. Instead I am getting the states listed over and over and the zip code listed over and over. Could someone please teach me how to do this properly. Below is a sample of the data being created and below that is my sql query.



CA	92345	7.75	0
CA	92345	7.75	2.32
CA	92345	7.75	2.55
CA	92345	7.75	2.79
CA	92345	7.75	3.1
CA	92346	7.75	1.39
CA	92363	7.75	2.79
CA	92371	7.75	3.1
CA	92372	7.75	0.46
CA	92374	7.75	0.23
CA	92376	7.75	0
CA	92392	7.75	0
CA	92392	7.75	0.92
CA	92392	7.75	2.32
CA	92392	7.75	2.79
CA	92392	7.75	11.61
CA	92392	7.75	15.5
CA	92394	7.75	0.46
CA	92395	7.75	0
CA	92395	7.75	0.15
CA	92395	7.75	1.86
CA	92395	7.75	2.32
CA	92397	7.75	0
CA	92399	7.75	2.32
CA	92399	7.75	15.5
CA	92404	7.75	0
CA	92404	7.75	2.32
CA	92405	8	0
CA	92405	8	1.76
CA	92407	7.75	0
CA	92407	7.75	0.79

Here is my query:



SELECT DISTINCT o.ShipState AS 'U.S. State', t.TaxPostalCode AS 'Zip Code', t.Tax1_Percent AS 'Tax Rate', SUM( o.SalesTax1 ) AS 'Total Sales Tax'
FROM Orders AS o
INNER JOIN Tax AS t ON o.ShipPostalCode = t.TaxPostalCode
GROUP BY o.ShipState, t.TaxPostalCode, t.Tax1_Percent, o.SalesTax1
ORDER BY o.ShipState, t.TaxPostalCode, t.Tax1_Percent

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2012-06-11 : 12:37:10

change
GROUP BY o.ShipState, t.TaxPostalCode, t.Tax1_Percent, o.SalesTax1
ORDER BY o.ShipState, t.TaxPostalCode, t.Tax1_Percent

To
GROUP BY o.ShipState, t.TaxPostalCode, t.Tax1_Percent
ORDER BY o.ShipState, t.TaxPostalCode

Jim
Users <> Logic

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources