Topic: Select Distinct & sum don't work together properly @佛州华人论坛:佛州枫下论坛 The Rolia Forum of Florida

工作学习 / 专业技术讨论 / Select Distinct & sum don't work together properlye.g. in a table with id (uniqueidentifier), cost(float)
without using subquery, is it possible to return distinct Id's total cost.
Select distinct id,sum(cost) doesn't work.
Any suggestion would be appreciated. -sunday8(sunday8); 2007-7-27 {216} (#3834052@0)

select id, sum(cost) from mytable group by id -xbox360(big麻); 2007-7-27 (#3834058@0)

Do you have other field(s) in query? If not, xbox360 ‘s query should get distinct results. -deep_blue(BLUE); 2007-7-28 (#3835779@0)

I see.You cannot use key word, DISTINCT, in Group By query, because Group By query gives distinct result from data source.

Your actual problem is duplicated record(s) in data source. You should filter out duplication first either using DISTINCT or Group By. -deep_blue(BLUE); 2007-7-30 {256} (#3838464@0)

distinct 可以和GROUP BY 连用。楼主只不过漏写了GROUP BY. distinct 的意思是：如果结果集中有完全一样的行，只留下其中的一行 -newkid(newkid); 2007-8-10 (#3862097@0)

It sounds cool. Actually LZ wanted: SELECT ID, SUM(DISTICT cost) FROM TABLE_NAME GROUP BY ID -deep_blue(BLUE); 2007-8-13 (#3865344@0)

I think you guys(newkid/BLUE) solved the problem I described in my post.But the problem in my senario was that I can't put the id in the query (e.g. group by). If I do, it will return more than rows of sum where I only need one (the total of distinct id's cost). So I can't avoid subquery and make a sum again againt the first query.

The initial intention of my post is to avoid subquery with possible combination of distinct and group by. It turned out to be an interesting discussion. and how easily a mistake can be made if one overlooks a simple query. -sunday8(sunday8); 2007-8-13 {494} (#3865606@0)

the example i gave out was an over-simplied version.There are other fields in the table. so sum is necessary.
But the case you described won't happen in my senario. -sunday8(sunday8); 2007-8-2 {115} (#3846510@0)

then which field need to sum? in your example, I cannot see any field needs to sum, why not give out a clear example when you ask a question? anyway, not my business -guestagain(guest again); 2007-8-3 (#3848054@0)

don't worry about it now. I found other way around it.The problem arose initially from sum() when I found some duplicate records I can't get rid of. and I don't want to put subquery which will cause performance issue. So i thought distinct will help me to filter the duplicate data. But at run time Sum function actually goes first before distinct. That was the source of the problem. -sunday8(sunday8); 2007-8-3 {332} (#3849051@0)

confused. If the ID is uniqueIdentifier, how can it be duplicated? -marsher(住家男人); 2007-8-8 (#3856204@0)

you can duplicate the same record into the table if the Id is not a (or not the only) key, can you? :) -sunday8(sunday8); 2007-8-8 (#3856466@0)
Although it’s a special data type, uniqueIdentifier is just only one data type like other types such as char, varchar, and so on. Don’t be confused by its name. -deep_blue(BLUE); 2007-8-8 (#3857218@0)

@Florida