[UPHPU] OT: SQL Query Question

[Brandon Stout]

If you do SUM(`items`.`dkpcost`), and/or
SUM(`raidattendence`.`PointsEarned`), you must use GROUP BY before you
use ORDER BY, and if you do GROUP BY, that often makes every row
distinct.  If you are summing things at another level than the query,
you should probably try SELECT DISTINCT, which ensures every row is
unique. Are you using MySQL, PostgreSQL, or some other database?

Brandon Stout
http://mscis.org

Jonathan Grotegut wrote:
> I know this is off topic but I am hoping someone with more experience
> with SQL than myself can help with a problem I am having.
>
> I am STILL working on the DKP system, I got stuck on a Query problem
> that I have now finally found the problem I am just unsure how to fix
> it.  Anyways, my question:
>
> Is it possible to do a query and specify in your query to use a
> distinct on a particular field in one of the databases.  Basically my
> query is as follows:
>
> SELECT
> `drops`.`raidID`,
> `player`.`playerID`,
> `player`.`PlayerName`,
> `player`.`PlayerClass`,
> `items`.`dkpcost`,
> `raidattendence`.`PointsEarned`,
> `raidattendence`.`raidID`
> FROM
> `player`
> Inner Join `raidattendence` ON `player`.`playerID` =
> `raidattendence`.`playerID`
> Left Join `drops` ON `raidattendence`.`playerID` = `drops`.`playerID`
> AND `raidattendence`.`raidID` = `drops`.`raidID`
> Left Join `items` ON `drops`.`itemID` = `items`.`itemID`
> WHERE
> `player`.`playerID` = 1
> ORDER BY
> `player`.`PlayerName` ASC
>
> I am doing a sum on `items`.`dkpcost` and another sum on
> `raidattendence`.`PointsEarned`  because of the way the data is it
> when a player gets more than one item on a raid their "Points Earned"
> is getting added in multiple times.  I need to prevent this from
> happening.
>
> Hopefully this makes some sort of sense, any suggestions are appreciated.
>
> TIA,
> Jonathan
>
> _______________________________________________
>
> UPHPU mailing list
> UPHPU at uphpu.org
> http://uphpu.org/mailman/listinfo/uphpu
> IRC: #uphpu on irc.freenode.net