# myqsl-abfrage mit group ect.



## HammerHe@rt (12. Januar 2003)

ich habe eine vote tabelle wo die votes eines users eingetragen werden

ich hab 20 spieler zur auswahl, wobei der user 3, 2 und 1 punkt vergeben kann...also sich 3 unterschiedliche spieler raussuchen kann

in der tabelle gibs die spalten id, threepoint, twopoint, onepoint, ip, date

die spieler werden mit ihrer id aus der spielerdatenbank eingetragen..

so das in threepoint, twopoint und onepoint nur zahlen drinn stehen


wie muss nun die Abfrage lauten damit ich  folgendes Ergebniss ausgeben kann

XX Votes

1. Spielername XX Punkte (gesamt) davon xx mal 3Pkt, xx mal 2Pkt, xx mal 1 Pkt.
2. -""-
3. -""-


kann mir wer da helfen?


hab bisher da leider noch kein ansatzpunkt, tu mir da immer bischen schwer bei so verschachtelten abfragen


----------



## melmager (13. Januar 2003)

spielerdb und punktedb müssen per join verbunden werden

select s.name,sum(p.threepoint) as three,sum(p.twopoint) as two,sum(p.onepoint) as one,(three+two+one) as rang
from spieler as s left join punkte as p on p.id=s.id group by s.id order by rang desc

ich hoffe ist richtig denke mal ja


----------



## HammerHe@rt (13. Januar 2003)

hmm nee da bekomm ich Fehlermeldung zurück...not suplied argument

ich denk auch nicht das das mit einer query gehen wird...

der Fehler liegt glaub ich irgendwo beim join

..... on p.id=s.id group by s.id


p.id (also id in der Punktetabelle)
die hat ja nix mit den Spielern an sich zu tun bzw den Namen...die ID's der Spieler stehen ja in threepoint, twopoit und onepoint drinne...

so das ein vote in der db so aussieht

ID - threepoint - twopoit - onepoint - ip - date
-------------------------------------------------
1  - 212        - 145     - 342      - xxx - xxx



also der bezug zum namen müsste dann auf threepoint/twopoint/onepoint liegen , was ja sicher nicht gehen wird....

am besten wär vielleicht ne abfrage das ich erstmal die punkte hab der besten 3 und dann kann ich ja mit ner 2. den namen abfragen durch die id


----------



## HammerHe@rt (13. Januar 2003)

imo hab ich selber so gelöst.....


```
<?
mysql_query("create temporary table vote (plid int not null,three int not null,two int not null,one int not null)");
mysql_query("insert into vote (plid,three) SELECT threestar, count(*) FROM `starvote` group by threestar");
mysql_query("insert into vote (plid,two) SELECT twostar, count(*) FROM `starvote` group by twostar");
mysql_query("insert into vote (plid,one) SELECT onestar, count(*) FROM `starvote` group by onestar");
$res = mysql_query("select plid, sum(three*3)+sum(two*2)+sum(one) as points, sum(three) as three, sum(two) as two, sum(one) as one from vote group by plid order by points desc limit 3");
?>
```


mir fehlt halt noch in der letzten query der left join zur tabelle spieler mit der spalte namen...
vielleicht kannste mir da helfen


oder du hast noch ne Idee wie ich das mit der temp.Tabelle umgehen kann...was ich aber nicht glaube *g*


----------



## melmager (13. Januar 2003)

schon klar 

aber ich bin einfach mal davon ausgegangen das deine spielerdb so aufgebaut ist:

id name ...

edit klar kann man die temp db umgehen 
durch join


----------



## HammerHe@rt (13. Januar 2003)

jo Spielerdatenbank is so aufgebaut....


ID name .....

12 Harald




also wo is dann der Fehler in deiner Query.....


weil du joints auf p.id

das geht doch aber nicht...da, da die id ja nur die id des eindeutigen votedatensatzes ist...und die spielerids in threepoints, twopooints und onepoint stehen...also auf was willste da joinen ? *g*


lass mich gerne belehren..also nur zu....


----------



## melmager (13. Januar 2003)

so das ein vote in der db so aussieht

ID - threepoint - twopoit - onepoint - ip - date
-------------------------------------------------
1 - 212 - 145 - 342 - xxx - xxx


ok das ist die tabelle starvote ...

wie sieht denn deine user tabelle aus?


----------



## melmager (13. Januar 2003)

ok lassen wir mal die punkte aussen vor
verbinden wir mal diespielerdb und die starvote tabelle:

select * from starvote left join spieler on spieler.id=starvote.id

damit werden die beiden tabellen über die gemeinsame id verbunden

dann kürzen wir mal:

select * from starvote as v left join spieler as s on s.id=v.id 

dann machen wir mal die summen:

select *.s,sum(v.onevote) from starvote as v left join spieler as s on s.id=v.id


----------



## HammerHe@rt (13. Januar 2003)

usertabelle gibts nicht...es können nur die voten die noch nicht mit ihrer ip in der tabelle stehn...

letzlich soll nur folgendes rauskommen bei der Abfrage aus Tabelle starvote

1.Spielername  xx Punkte (2x3pkt/1x2pkt/0x0Pkt.)
2. gleiche 
3. gleiche




dazu muss man wie du schon bemerktest auf die Spielertabelle joinen um die Zahlen 212, 145, 342 (siehe letztes Bsp.) in die richtigen Namen "umzuwandeln".....

Spielerdatenbank sieht so aus......

ID - Name - rest
212 - Paul
145 - Hans
342 - Erna



so...auf was willste denn jetzt die spieler.id in der tabelle starvote joinen ?


----------



## HammerHe@rt (13. Januar 2003)

nein da liegt dein denkfehler im ansatz !!!

select * from starvote left join spieler on spieler.id=starvote.id 


starvote.id hat absolut nix mit der spieler.id zu tun

die spieler id's stehen in der Tabelle Starvote in den spalten threepoint, twopoint, onepoint...

die id in starvote ist nur die normale datensatzid für einen vote

verstanden? *g*


----------



## melmager (13. Januar 2003)

ach sooooooo ..... 

in onevote steht also die ip vom user dann muss ich noch einwenig grübeln ...


----------



## HammerHe@rt (13. Januar 2003)

hehe onevote gibs nicht *g*

am besten ich fasse nochmal zusammen *g*

der user kann bei einem Vote 3 spieler wählen...der 1.bekommt 3 pkt, der 2. 2 und der 3. 1 Punkt


Tabelle Starvote (1Datensatz = 1 Vote eines Users...durch IP geloged)
---------------------------------------------------------------------
ID (autoincrement, primary key) - DatensatzID
threepoint (int) - feld für spielerid welcher 3 pkt beim vote bekommen hat
twopoint (int) - feld für spielerid welcher 2 pkt beim vote bekommen hat
onepoint (int) feld für spielerid welcher 1 pkt beim vote bekommen hat
ip (varchar15) - IP des voters (nur um mehrfachvotes zu verhindern)
date - datum des votes (für spätere monatliche abfrage der votes)


Tabelle der Spieler die gevotet werden können
--------------------------------------------------------
ID - eindeutige SpielerID
name - Spielername
und noch weitere Felder welche hier nicht von bedeutung sind..




so und rauskommen soll letztlich eine TOP 3 Liste...also die 3 besten Spieler nach Punkten:

1. Name xx Punkte (1,2,2)
2. Name xx Punkte (1,2,1)
3. Name xx Punkte (0,0,1)


wobei in Klammern die Anzahl der 3Punkte, 2 Punkte und 1Punkte votes angezeigt werden...


soo ich hoffe das nochmal verständlich nahegebracht zu haben ^^


----------



## melmager (13. Januar 2003)

gings nicht noch komplizierter 

select * from starvote as v left join spieler as s on v.onepoint=s.id,v.twopoint=s.id,v.threepoint=s.id group by s.id

teste bitte mal den befehl aus

dann die erweiterung

statt *

s.name,sum(v.threepoint)as drei,sum(v.twopoint) as zwei,sum(v.onepoint) as eins

wenn das geht erweitern um
(drei + zwei + eins) as rang

dann hinten dran

order by rang desc


----------



## HammerHe@rt (13. Januar 2003)

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource


----------



## HammerHe@rt (13. Januar 2003)

so habs nu so....

$res = mysql_query("select s.name,sum(p.threestar) as three,sum(p.twostar) as two,sum(p.onestar) as one,(three+two+one) as points from spieler as s left join starvote as p on p.threestar=s.id,p.twostar=s.id,p.onestar=s.id group by s.id order by points desc limit 3");


musste noch anpassen..die felder heissen threestar, twostar und onestar

aber geht trotzdem so nicht...
eigentlich gleich beim leftjoin schon nicht...


Fehlermeldung: not suplied mysql argument


----------



## HammerHe@rt (13. Januar 2003)

mich würde viel eher interessieren wie du die Abfrage hinbekommst erstmal ohne das join...das halt erstmal nur die zahlen(spielerids) dastehen....weil nach was willste groupen? kann man das in 3 spalten machen gleichzeitig?


----------



## melmager (13. Januar 2003)

nachtrag die sachen nach on
müssen mit or verbunden werden...

v.onepoint=s.id or v.twopoint=s.id or v.threepoint=s.id


----------



## HammerHe@rt (13. Januar 2003)

erstmal schön das du noch wach bist und dir soviel mühe machst...danke ^^

aber geht leider immernoch nicht

hier die query

$res = mysql_query("select s.name,sum(p.threestar) as three,sum(p.twostar) as two,sum(p.onestar) as one,(three+two+one) as points from spieler as s left join starvote as p on p.threestar=s.id or p.twostar=s.id or p.onestar=s.id group by s.id order by points desc limit 3");


und hier die Fehlermeldung

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource


----------



## melmager (13. Januar 2003)

habe eben mal ein kleine version deiner db gemacht und siehe da es geht nicht auf einmal :-(

achja statt sum muss es cout sein aber das ist nur kleinkram 

also es werden doch 3 abfragen ....

select count(one) select from starvote group by one;

usw mit
zwei und drei

PS
der jetzt ins bett geht 
PPS
bis morgen


----------



## HammerHe@rt (13. Januar 2003)

also is meine version mit der temp table garnet so schlecht?


wie muss dann die letzte query lauten für nen left join auf spieler...das raff ich nu überhaupt nicht *G*


```
<?
mysql_query("create temporary table vote (plid int not null,three int not null,two int not null,one int not null)");
mysql_query("insert into vote (plid,three) SELECT threestar, count(*) FROM `starvote` group by threestar");
mysql_query("insert into vote (plid,two) SELECT twostar, count(*) FROM `starvote` group by twostar");
mysql_query("insert into vote (plid,one) SELECT onestar, count(*) FROM `starvote` group by onestar");
$res = mysql_query("select plid, sum(three*3)+sum(two*2)+sum(one) as points, sum(three) as three, sum(two) as two, sum(one) as one from vote group by plid order by points desc limit 3");
?>
```


----------



## melmager (14. Januar 2003)

ja man könnte es mit einer temp db lösen 

aber das ding hat mein ehrgeiz geweckt *g*
nach einem guten frühstück und ein pott kaffee kam mir die eingebung 

melmager prodly presends : the monster sql   query *smile*

ich war schon auf dem richtigen weg nur müssen die tabellen anders mit einander verbunden werden:
ein kleines tut wie ich dazu gekommen bin:

select u.name,count(p3)*3 as c3 from spieler as u left join starvote as p3 on u.id=p3.threestar group by u.id

hier wird die usertabelle mit der punktetabelle verbunden (wobei die usertabelle vollständig erhalten bleibt) und wir zählen die einträge mit count (die mal der wertigkeit)
damit hätten wir schon mal die tabelle mit den dreierpunkten
da bei mysql von links nach rechts abgearbeitet wird können wir das ding erweitern 
jetzt hauen wir die zweite verküpfung auf das ergebnis:

select u.name,count(p3)*3 as c3,count(p2)*2 as c2 from spieler as u left join starvote as p3 on u.id=p3.threestar left join starvote as p2 on u.id=p2.twostar group by u.id

wir verküpfen das ergebins der ersten joins wieder mit der punktetabelle ...
und weil so schön ist ein drittes mal:

select u.name,count(p3)*3 as c3,count(p2)*2 as c2,count(p1) as c1 from spieler as u left join starvote as p3 on u.id=p3.threestar left join starvote as p2 on u.id=p2.twostar left join starvote as p1 on u.id=p1.onestar group by u.id

damit hätten wir schon mal ein ergebnis   das wir nur noch durch eine sortiermaschine schicken müssen:
sprich noch hinten dran:
order by c3 desc,c2 desc,c1 desc

als ergebis hast du dann namen anzahl der 3 punkte; anzahl der 2 punkte und anzahl der einerpunkte und das hübsch sortiert 
gesamtpunkte machste dann in php nach der abfrage

$row = mysql_fetch_array();
$gesamt =  $row['c3'] + $row['c2'] + $row['c1'];

......
PS: auch was dazugelernt hat


----------



## HammerHe@rt (14. Januar 2003)

hmm also hab mal die kleine oben zum anfang genommen und kommt fehler

select u.name,count(p3)*3 as c3 from spieler as u left join starvote as p3 on u.id=p3.threestar group by u.id 


des muss glaub ich count(p3.threestar) heissen sonst sagt er not valid argument....


tjo und dann hab ich als ergebnisse aaaaaaallllleeee spieler die in der spielertabelle sind und das sind ne menge...

rechnet sich das dann von der rechenzeit gegenüber meiner 3 fachen query...mal deine in Ehren *g*


----------



## melmager (14. Januar 2003)

> des muss glaub ich count(p3.threestar) heissen sonst sagt er not valid argument....



stimmt  
und die sortierung ist nicht ganz richtig so wie sie jetzt ist ist ein dreier mehr wert wie 2 zweier ...


select u.name,count(p3.threestar) as c3,count(p2.twostar) as c2,count(p1.onestar) as c1,(count(p3.threestar)*3+count(p2.twostar)*2+count(p1.onestar)) as rang from spieler as u left join starvote as p3 on u.id=p3.threestar left join starvote as p2 on u.id=p2.twostar left join starvote as p1 on u.id=p1.onestar group by u.id order by rang desc,c3 desc,c2 desc,c1 desc

so jetzt habe ich es (und auch die summe) *g*

zumindest haste jetzt ein risen sql ding  

nachtrag:
achja rechenzeit sollte die gleiche sein ... 

was die ausgabe betrifft kannst ja noch ein limit 10 anhängen für die top 10 liste


----------



## HammerHe@rt (14. Januar 2003)

schön also du siehst ich mach mir da auch schon meine gedanken dazu *g*

aber muss schon sagen respekt...hätt ich nie so hinbekommen...

du ahnst es sicherlich schon ^^
es gibt da noch ein problem *g*


da dort alle votes reinkommen in starvote und die ausgabe aber monatlich gemacht werden soll muss noch sowas rein...

where month(date)=month(now())

wobei date ein feld in starvote ist...halt der zeitpunkt des votes eines users....

wie muss ich da date joinen damit es funztz...nur p3 oder nur p2 oder nur p1 bringen mir da immer unterschiedliche ergebnisse..

na noch nen Kaffeeee ? *g*


----------



## melmager (14. Januar 2003)

> wie muss ich da date joinen damit es funztz...nur p3 oder nur p2 oder nur p1 bringen mir da immer unterschiedliche ergebnisse..



alle drei  auf das die query länger wird  (biste auf ein weltrecord scharf ? *g*)

where month(p1.date)=month(now()) or month(p2.date)=month(now()) or month(p3.date)=month(now())


----------



## melmager (14. Januar 2003)

bei deiner temp db muss der join so aussehen:

$res = mysql_query("select s.name,v.plid, sum(v.three*3)+sum(v.two*2)+sum(v.one) as points, sum(v.three) as three, sum(v.two) as two, sum(v.one) as one from vote as v left join spieler as s on s.id=v.plid group by v.plid order by points desc limit 3");

nur mal so 

jetzt haste die wahl


----------



## loki2002 (15. Januar 2003)

ich glaube mehrere SUM() ergebnisse in einem query zu addieren kannste vergessen, Mysql scheint damit "noch" Probleme zu haben ... keine Ahnung woran es liegt, aber das Problem hatten schon viele unter anderem auch ich  ... vielleicht ein Bug? (..)


----------



## HammerHe@rt (15. Januar 2003)

hmm kann aber bisher da keine Probleme feststellern....

er query'ed perfekt und gibt die richtigen Ergebnisse aus....(selber nachgerechnet *g*)


----------



## melmager (15. Januar 2003)

@loki mit den rechenergebnissen von mysql hatte ich noch nie probleme ...

haste da mal ein link oder so?

ich mache jedemenge auswertungen mit php und mysql ..und da sollte ich nachhaken wenn das stimmt...


----------



## HammerHe@rt (15. Januar 2003)

@melmager

danke nochmal für deine Hilfe...
deine sql war sogar im paar ms schneller als meine 3 *g*


wenn du soviel auswertungen mit php und sql machst vielleicht kannst mir ja nochmal bei einem Problem helfen *g*



so gebe ich eine Tabelle aus bei mir (besteht aus 15 teams)


```
$res=mysql_query("select tmptab2.team as id,teams.name as team,sum(games) as cnt,sum(th) as tgh,sum(tg) as tgg,sum(pt) as pts,sum(th-tg) as td from tmptab2 left join teams on tmptab2.team=teams.id group by tmptab2.team order by pts desc,cnt,td desc,tgh desc");
```

wenn du fragen dazu hast ...kann ich dir jederzeit beantworten *g*


aber so gibt er mir halt die teams aus nach diversen zeugs sortiert...



nu würd ich aber gerne nicht alle teams ausgegeben haben sondern nur 

den 1.
.
.
.
platz vor meinem verein (team = 1 = esw)
esw (team = 1 = esw)
platz nach meinem verein (team = 1 =esw)
.
.
.
platz 15.

wobei ja mein team auch 1. und 15. sein könnte (letzteres ist im moment der fall *g*)

also ich glaube das das weniger mit der query zu tun hat als mehr mit der logik des problems....

ich glaub als erstes muss ich mal nur den platz meines teams rausfinden und dann daraufhin weiterarbeiten-....wegen team platz -1 und +1


----------



## melmager (15. Januar 2003)

also das ist eine drei schritt gschichte:

1. ermitteln der punkte deines teams 

2. where $meinpkt > punkte limit 1,1

3. where $meinpkz < punkte limit 1,1

das ist möglichkeit a


du macht die abfrage wie gehabt und sotierst per php aus..

```
$erg = mysql_query(bla bla bla);
$zeilen = mysql_num_rows($erg);
$lo = 0;
do {
$vor = $row;
$row = mysql_fetch_array($erg);
$lo++
} while ($row['name'] <> "mein team");
if ($lo == 1) {
 echo "Wir sind nummer Eins";
} else {
 echo "Vor uns ",$vor['name'],"<br>";
}
echo "Wir sind das ",$row['name'],"<br>";
if ($lo > $eilen) {
 echo " wir sind letzter <br>";
} else {
$row = mysql_fetch_array($erg);
echo "Nach uns ",$row['name'],"<br>";
}
```

das ist möglichkeit b

c gibt es sicher auch ich komme nur nicht drauf ...


----------

