PHP SQL - MySQL DISTINCT SUM **SOLVED**

Author

MySQL DISTINCT SUM **SOLVED**

tranphp

2004-08-18, 3:57 pm

I am trying to do a select statement using distinct records and
retrieving the sum for a particular field.

Problem is the SELECT DISTINCT ...SUM(X) ...GROUP BY (Y) does not sum
up "X" properly. It instead sums up "X" from all
records grouped by "Y" as if there was no
"DISTINCT" in the query.

In other words,

If I perform a SELECT without DISTINCT, I get duplicate records.

If I perform a SELECT DISTINCT ...SUM(X) ...GROUP BY (Y), the sums for
"X" include "X" for all duplicate records of
"Y".

Is this a bug, or am I going about this the wrong way? Can someone
help?

----------------------------------------
The post originated from PHP Freaks:
----------------------------------------
http://www.phpfreaks.com
http://www.phpfreaks.com/forums

Barand

2004-08-18, 3:57 pm

If you have table1 like

id | col1

1 | a
2 | a
3 | b
4 | c
5 | c

--------
SELECT DISTINCT col1 FROM table1
--------
gives just the unique values
> a
> b
> c

--------
SELECT col1 FROM table1 GROUP BY col1
--------
gives the unique values again
> a
> b
> c

So if the DISTINCT keyword hadn't been invented then GROUP BY will
do the job. The main use of GROUP BY, though is to aggregate COUNTs
or SUMs of values by records which have a common field value

--------
SELECT col1, COUNT(*) FROM table1 GROUP BY col1
--------
gives the unique values again with record counts for each
> a | 2
> b | 1
> c | 2

hth

Barand

http://members.aol.com/barryaandrew...agridguide.html easy
data tables - and more

----------------------------------------
The post originated from PHP Freaks:
----------------------------------------
http://www.phpfreaks.com
http://www.phpfreaks.com/forums

tranphp

2004-08-18, 3:57 pm

Great explanation, Barand. I understand it a lot better. I got some
of my queries to work, but the ones with LEFT JOINs are not
cooperating. I still get the same results.

To elaborate......

4 tables:

event: eventid,
game: gameid, eventid
stat: statid, gameid, playerid, pts
player: playerid, firstname

I am trying to SELECT all the "Stat"s that are in
"Event" 1, group them by playerid, and then add
"pts" for each "playerid"

The following selects all playerid in eventid=7 along with pts total

SELECT DISTINCT stat.playerid, stat.pts, player.firstname
FROM stat
LEFT JOIN game ON game.gameid=stat.gameid
LEFT JOIN event ON event.eventid=game.eventid
LEFT JOIN player ON player.playerid=stat.playerid
WHERE event.eventid=7
GROUP BY stat.playerid

Problem: The pts total is as if DISTINCT was not included in the
statement.

What can I do?

----------------------------------------
The post originated from PHP Freaks:
----------------------------------------
http://www.phpfreaks.com
http://www.phpfreaks.com/forums

tranphp

2004-08-18, 3:57 pm

Again, you are a genius!!! Needless to say, I don't
know much about php, mysql, or coding as a matter of fact, but when I
get a response from the Barand, I always know I'll get a clear
explanation.

Thanks for the tip on LEFT JOIN.

----------------------------------------
The post originated from PHP Freaks:
----------------------------------------
http://www.phpfreaks.com
http://www.phpfreaks.com/forums

Sponsored Links