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 2008 Forums
 Transact-SQL (2008)
 distinct summing?

Author  Topic 

mmiddl
Starting Member

1 Post

Posted - 2013-01-17 : 13:51:02
I'm so stumped I can't even google this! I've got a distinct query figured out, but then I want to sum up one column in the query results & can't figure it out. Example:

select distinct t1.basket, t1.fruit, t1.qty, t2.size
from table1 t1, table2 t2
where t1.basket=t2.basketID

Table1

Basket/ Fruit/ Qty
1/ apple/ 10
1/ apple/ 10
1/ apple/ 1
2/ apple/ 2
3/ orange/ 3

Table 2
Basket/Size
1/small
2/medium
3/large

Qry results:
Basket/ Fruit/ Qty/ Size
1/ apple/ 10/ small
1/ apple/ 1/ small
2/ apple/ 2/ medium
3/ orange/ 3/ large

So the whole point of the distinct qry is that it removed that repeated result where we had basket 1 of 10 apples removed.

I WANT to find total of qty which=16.

any thoughts on how I can use SUM to add up the result of 10+1+2+3 and just return: 16 to me?

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2013-01-17 : 14:28:23
[code]Select SUM(Qty)
from
(
Select distinct t1.basket, t1.fruit, t1.qty, t2.size
from table1 t1, table2 t2
where t1.basket=t2.basketID
)P[/code]
Go to Top of Page
   

- Advertisement -