find rows with different data - 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

SQL Server 2005 Forums

Transact-SQL (2005)

find rows with different data

Author

Topic

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2010-03-02 : 08:05:10

Hi,

I have a table with the following:

Item Carton Price Key Country
12 0012 23 6 TH
12 0012 12 7 TH
13 2309 18 7 uk
14 0099 1 9 US
14 0099 1 9 FR

What i need to do is find rows that have same item and carton but different keys and same country. In that case i have to sum the price.
In this example the first 2 rows will sum up to 35.

How can i do this query?

Thanks

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-03-02 : 08:09:28

Do you mean this:

SELECT Item, Carton, SUM(Price), Country
FROM table_name
GROUP BY Item, Carton, Country
HAVING MIN(Key) < MAX(Key)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-02 : 08:09:28

select item,carton,coutry
,sum(price) as price from your_table
group by item,carton,coutry
having count(*)>1

Madhivanan

Failing to plan is Planning to fail

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2010-03-02 : 08:10:03

select item, carton,country,sum(price)
from t
group by
item, carton,country

????

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-02 : 08:10:41

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-02 : 08:39:24

quote:
Originally posted by LoztInSpace

select item, carton,country,sum(price)
from t
group by
item, carton,country

????

this will just return sum of all possible groups of item, carton,country. you need to put a condition to ensure you return only rows that have same item and carton but different keys and same country as done by other posters

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources