SELECT * but without the duplicated values...

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

Other Forums

MS Access

SELECT * but without the duplicated values...

Author

Topic

sugar
Starting Member

10 Posts

Posted - 2005-07-08 : 21:11:34

hi, i have a big big table and my results are this kind of:
SKU,REVENUE
----------
a002,20
a002,35
b017,1
b017,14

(NOTE THE DUPLICATED SKU VALUES)

the count is more than 78,000 (it cant be opened with excel[65,000MAX] or with openoffice[32,000MAX].
So im working with MS ACCESS.

This is my SQL code which generates that table:


SELECT [Order Details].SKU, [Order Details].ShippedSubtotal
FROM [Order Details]
WHERE [Order Details].ShippedSubtotal > 0;

My question is little bit challenging...
How can i edit the sql code in order to display all the SKU values but with no duplicated and the sum of Revenue for those duplicated SKU, see this table, this is what i need:

SKU,REVENUE
----------
a002,55
b017,15

does it made sense... can help me please?

thanks in advance.

Aldo

nr
SQLTeam MVY

12543 Posts

Posted - 2005-07-09 : 13:49:09

select SKU, REVENUE = sum(ShippedSubtotal)
FROM [Order Details]
WHERE ShippedSubtotal > 0
group by SKU

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

sugar
Starting Member

10 Posts

Posted - 2005-07-09 : 14:05:35

Thanks! but if use that SQL query... it will remove the duplicated SKU records?

i will try it right now..

thanks

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-07-09 : 14:57:36

If you don't know how to do this, you need to take a course in the basics of SQL.

Here are a couple of free online courses that are a good introduction to the basics of SQL. They should be taken in sequence.

http://www.sqlcourse.com/
http://sqlcourse2.com/

This SQL Course includes a page where you can run your own queries, and a quiz.:
http://www.w3schools.com/sql/default.asp

These courses are not meant to cover advanced features and programming techniques. Some of these courses use versions of SQL with certain features not available in SQL Server. Please refer to SQL Server Books Online for the exact syntax for SQL Server Transact-SQL.

CODO ERGO SUM

sugar
Starting Member

10 Posts

Posted - 2005-07-09 : 15:00:16

ok thanks for the advice.

sugar
Starting Member

10 Posts

Posted - 2005-07-11 : 17:28:02

quote:
Originally posted by nr

select SKU, REVENUE = sum(ShippedSubtotal)
FROM [Order Details]
WHERE ShippedSubtotal > 0
group by SKU

Thanks nr!!
based on your imput i can solve the issue!

SELECT [Order Details].SKU AS Local_SKU, Sum([Order Details].ShippedSubtotal) AS Revenue
FROM [Order Details]
GROUP BY [Order Details].SKU
ORDER BY [Order Details].SKU;

thanks!!!

harjinder
Starting Member

3 Posts

Posted - 2005-08-04 : 01:49:56

select * from tablename order by * having count(*)<1
It will extract the unique records from table

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-04 : 01:58:36

>>select * from tablename order by * having count(*)<1

Did you try it anyway?

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources