Berechnung in MySQL-Abfrage

maeck · 28.09.2007, 11:37

Hallo,

ich habe zwei Tabellen, eine mit Familienmitgliedern und die andere mit den Familien.

Nun habe ich eine Abfrage, die mir alle Familienmitglieder mit den Familiendaten anzeigt:

Code:

SELECT fam.id, 
fam.lastname,  
mem.surname, 
mem.birthday, 
mem.lodgingid, 
mem.mealid,
fam.participationfee
FROM anm2007_family_members mem, anm2007_families fam
WHERE mem.famid=fam.id

Das funktioniert auch alles.

Nun muss ich in der Spalte mem.lodgingid die Werte (1,2,3,4,5,6) durch Zahlen ersetzen (für 2,3,4=20 und für 5=33 und 1,6=0).
Ich habe sowas mit Mysql noch nicht gemacht, geht das überhaupt?

Zweites Problem: In der Spalte fam.participationfee steht, ob die Familie Tagungsgebür zahlen muss (1) oder nicht (0). Bei 1 muss ich es ersetzen mit 80 geteilt durch die Anzahl der Familienmitglieder (also die Gebühr muss für alle Personen aufgesplittet werden).
Die Anzahl bekomm ich über

Code:

SELECT count(*) FROM `anm2007_family_members` WHERE famid='$id'

Aber ist es überhaupt möglich die Dinge zusammen zu bringen?

Danke für eure Hilfe und schöne Grüße,
Marcel

Slava · 30.09.2007, 23:18

Zitat:

Nun muss ich in der Spalte mem.lodgingid die Werte (1,2,3,4,5,6) durch Zahlen ersetzen (für 2,3,4=20 und für 5=33 und 1,6=0).

das habe ich nicht ganz verstanden. sind die werte einfach als string in der spalte abgespeichert?
wenn ja, dann muss du mit update und replace alles ersetzen etwa so
update tablename
set lodgingid =REPLACE(lodgingid,',4,',',20,')
die verschahtelte replace ist natürlich erlaubt.

Ich habe ein wenig Zweifel, dass die tabellen gut ausgedacht sind, da solche datenhaltung nur bei 'SET' - feldern ein Sinn macht.

zu der zwiter frage:
da du nicht so stark in sql bist, würde ich dir subquery vorschlagen wenn deine datenbankversion das erlaubt.
welche version von mysql hast du?

maeck · 01.10.2007, 10:22

Hallo Slava,

danke für deine Antwort.
Ich glaube ich habe einen wichtigen Punkt vergessen. Mir geht es nicht darum, die Werte innerhalb der Tabelle zu verändern, die sind schon richtig so, denke ich (Hintergrund: In der lodgingid-Spalte stehen nur zahlen, die mit einer anderen Tabelle verknüpft sind, in der dann zu der Zahl die entsprechende Unterkunft steht, die derjenige gewählt hat).

Ich will einen csv-Export über eine SQL-Abfrage generieren. Und ich will wissen, ob es mit MySQL möglich ist solche "Berechnungen" zu tun, denn dann könnte ich es direkt über phpMyAdmin generieren. Sonst muss ich extra ein PHP-Skript schreiben, was mir die Berechnungen macht und das darüber generieren.

Meine MySQL-Version ist 5.0.19

Gruß Marcel

lazydog · 01.10.2007, 13:04

Das erste Problem lässt sich mit

Code:

SELECT
    fam.id, 
    fam.lastname,  
    mem.surname, 
    mem.birthday, 
    CASE  WHEN mem.lodgingid = 1 OR mem.lodgingid = 6 THEN 0 
          WHEN mem.lodgingid = 3 THEN 33
          ELSE 20 END                            AS LODGINGID,
    mem.mealid, 
    fam.participationfee * 80 / $anz_member
FROM anm2007_family_members mem
INNER JOIN anm2007_families fam ON mem.famid=fam.id

lösen. Das zweite Problem ist so natürlich noch nicht gelöst, da $anz_member ja noch nicht bekannt ist. Muss ich mir noch überlegen.

maeck · 01.10.2007, 14:19

Hallo lazydog,

hey super, danke dir!
Das erste funktioniert schon mal prima. Bin gespannt, ob das mit der Anzahl auch noch möglich ist.

Danke für deine Bemühungen!
Gruß Marcel

lazydog · 01.10.2007, 15:41

Ok, ich habs versucht, aber keinen Weg gefunden das in einer Abfrage zu lösen. Das einzige, was mir eingefallen ist: du definierst eine Funktion (Stored Procedure) und rufst diese dann in der Abfrage auf. Die Funktion musst du in der MySQL-Konsole definieren, phpmyadmin unterstütz das nicht. Das brauchst du allerdings nur einmal zu tun, dann bleibt sie bestehen, bis du sie wieder löschst. Da die Funktion aus mehreren, mit Semikolon getrennten Befehlen besteht, müssen wir zuerst den Delimiter ändern z.B:

Code:

delimiter $$

dann die Funktion:

Code:

CREATE FUNCTION anz_member(fam_id INTEGER)
       RETURNS INTEGER
BEGIN
	DECLARE anz INTEGER DEFAULT 0;
    SELECT COUNT(*) INTO anz
           FROM anm2007_family_members 
           WHERE famid = fam_id;
    RETURN anz; 
END$$

Anschliessend kannst du den Delimiter wieder auf Semikolon setzen. Die Abfrage sieht dann so aus:

Code:

SELECT 
    fam.id, 
    fam.lastname,  
    mem.surname, 
    mem.birthday, 
    CASE  WHEN mem.lodgingid = 1 OR mem.lodgingid = 6 THEN 0 
          WHEN mem.lodgingid = 3 THEN 33 
          ELSE 20 END                            AS LODGINGID, 
    mem.mealid, 
    fam.participationfee * 80 / anz_member(fam.id) AS FEE
FROM anm2007_family_members mem 
INNER JOIN anm2007_families fam ON mem.famid=fam.id

Soviel zur Theorie, getestet hab ichs nicht.

Slava · 01.10.2007, 23:21

nicht getestet

Code:

SELECT 
    fam.id, 
    fam.lastname,  
    mem.surname, 
    mem.birthday, 
    ELT(FIELD(mem.lodgingid,1,2,3,4,5,6),0,20,20,20,33,0)  AS LODGINGID, 
    mem.mealid, 
    fam.participationfee * 80 / 
(select count(*) from anm2007_family_members where famid=fam.id) as beitrag
INNER JOIN anm2007_families fam ON mem.famid=fam.id
FROM anm2007_family_members mem 
INNER JOIN anm2007_families fam ON mem.famid=fam.id

Edit: nee man muss noch der fall abfangen, wenn mem.lodgingid nicht im wertbereich von 1 bis 6 liegt. also noch eine if() muss rein.
oder so wie es vorgeschlagen wurde
CASE WHEN mem.lodgingid = 1 OR mem.lodgingid = 6 THEN 0
WHEN mem.lodgingid = 5 THEN 33
WHEN mem.lodgingid in(2,3,4) THEN 20
ELSE mem.lodgingid
END CASE

maeck · 04.10.2007, 21:42

Ihr seid echt super, vielen Dank!
Werde es testen und mich dann nochmal melden.

Schöne Grüße,
Marcel

maeck · 09.10.2007, 23:22

Nach einigem rumprobieren, hat es mit eurer Hilfe geklappt.
Vielen Dank nochmal!

Themen-Optionen
Druckbare Version zeigen Jemanden per E-Mail auf dieses Thema hinweisen
Thema bewerten
Thema bewerten:

Ähnliche Themen
Thema	Autor	Forum	Antworten	Letzter Beitrag
Frage: richtige SQL Abfrage von einer MySQL Spalte die SET werte besitzt	Seb	PHP Tipps 2008	2	29.05.2008 11:36
MySQL Abfrage von Ver. 5 für Ver 3. des MySQL Servers	FireFIghter	Datenbanken	3	02.02.2007 17:18
MySQL Abfrage macht Seite langsam		Datenbanken	11	19.10.2006 19:38
MySQL Abfrage Von / Bis	andrew22	Datenbanken	1	02.10.2006 11:04
MySQL - Abfrage: Unterschiedliche Ergebnisse aus PHP/manuell	horstenpeter	Datenbanken	3	16.07.2006 13:22
mysql abfrage in Schleife	havok	PHP Tipps 2006	6	09.05.2006 08:39
mysql abfrage	mqs	PHP Tipps 2006	4	09.04.2006 17:57
Abfrage bei mySQL 4	ApfeL	Datenbanken	5	24.03.2006 18:53
komplizierte mysql abfrage	php_frage	PHP Tipps 2007	5	28.12.2005 17:55
[Erledigt] mysql abfrage über 4 Tabelle - bis 3 geht, bei der 4. habert		Datenbanken	2	08.09.2005 11:59
Suche Tipps für Persormance-Steigerung (Geld für Nützliches)		Beitragsarchiv	18	16.08.2005 10:57
[Erledigt] Mysql Abfrage Problem!		PHP-Fortgeschrittene	5	27.11.2004 10:22
komplizierte mySQL Abfrage		Datenbanken	6	25.06.2004 15:34
Paradox, Fehler bei Berechnung in MySQL	MrNiceGuy	Datenbanken	0	23.06.2004 16:09
[Erledigt] mysql Abfrage		Datenbanken	5	18.06.2004 14:16

28.09.2007, 11:37
maeck Erfahrener Benutzer Registriert seit: 28.06.2008 Beiträge: 259	Berechnung in MySQL-Abfrage Hallo, ich habe zwei Tabellen, eine mit Familienmitgliedern und die andere mit den Familien. Nun habe ich eine Abfrage, die mir alle Familienmitglieder mit den Familiendaten anzeigt: Code: SELECT fam.id, fam.lastname, mem.surname, mem.birthday, mem.lodgingid, mem.mealid, fam.participationfee FROM anm2007_family_members mem, anm2007_families fam WHERE mem.famid=fam.id Das funktioniert auch alles. Nun muss ich in der Spalte mem.lodgingid die Werte (1,2,3,4,5,6) durch Zahlen ersetzen (für 2,3,4=20 und für 5=33 und 1,6=0). Ich habe sowas mit Mysql noch nicht gemacht, geht das überhaupt? Zweites Problem: In der Spalte fam.participationfee steht, ob die Familie Tagungsgebür zahlen muss (1) oder nicht (0). Bei 1 muss ich es ersetzen mit 80 geteilt durch die Anzahl der Familienmitglieder (also die Gebühr muss für alle Personen aufgesplittet werden). Die Anzahl bekomm ich über Code: SELECT count(*) FROM `anm2007_family_members` WHERE famid='$id' Aber ist es überhaupt möglich die Dinge zusammen zu bringen? Danke für eure Hilfe und schöne Grüße, Marcel


Mister Ad PHP Code Flüsterer Registriert seit: 21.08.2005 Beiträge: 4682 PHP-Kenntnisse: Fortgeschritten

01.10.2007, 10:22
maeck Erfahrener Benutzer Registriert seit: 28.06.2008 Beiträge: 259	Hallo Slava, danke für deine Antwort. Ich glaube ich habe einen wichtigen Punkt vergessen. Mir geht es nicht darum, die Werte innerhalb der Tabelle zu verändern, die sind schon richtig so, denke ich (Hintergrund: In der lodgingid-Spalte stehen nur zahlen, die mit einer anderen Tabelle verknüpft sind, in der dann zu der Zahl die entsprechende Unterkunft steht, die derjenige gewählt hat). Ich will einen csv-Export über eine SQL-Abfrage generieren. Und ich will wissen, ob es mit MySQL möglich ist solche "Berechnungen" zu tun, denn dann könnte ich es direkt über phpMyAdmin generieren. Sonst muss ich extra ein PHP-Skript schreiben, was mir die Berechnungen macht und das darüber generieren. Meine MySQL-Version ist 5.0.19 Gruß Marcel

01.10.2007, 14:19
maeck Erfahrener Benutzer Registriert seit: 28.06.2008 Beiträge: 259	Hallo lazydog, hey super, danke dir! Das erste funktioniert schon mal prima. Bin gespannt, ob das mit der Anzahl auch noch möglich ist. Danke für deine Bemühungen! Gruß Marcel

01.10.2007, 15:41
lazydog Erfahrener Benutzer Registriert seit: 02.04.2008 Beiträge: 4.032	Ok, ich habs versucht, aber keinen Weg gefunden das in einer Abfrage zu lösen. Das einzige, was mir eingefallen ist: du definierst eine Funktion (Stored Procedure) und rufst diese dann in der Abfrage auf. Die Funktion musst du in der MySQL-Konsole definieren, phpmyadmin unterstütz das nicht. Das brauchst du allerdings nur einmal zu tun, dann bleibt sie bestehen, bis du sie wieder löschst. Da die Funktion aus mehreren, mit Semikolon getrennten Befehlen besteht, müssen wir zuerst den Delimiter ändern z.B: Code: delimiter $$ dann die Funktion: Code: CREATE FUNCTION anz_member(fam_id INTEGER) RETURNS INTEGER BEGIN DECLARE anz INTEGER DEFAULT 0; SELECT COUNT() INTO anz FROM anm2007_family_members WHERE famid = fam_id; RETURN anz; END$$ Anschliessend kannst du den Delimiter wieder auf Semikolon setzen. Die Abfrage sieht dann so aus: Code: SELECT fam.id, fam.lastname, mem.surname, mem.birthday, CASE WHEN mem.lodgingid = 1 OR mem.lodgingid = 6 THEN 0 WHEN mem.lodgingid = 3 THEN 33 ELSE 20 END AS LODGINGID, mem.mealid, fam.participationfee 80 / anz_member(fam.id) AS FEE FROM anm2007_family_members mem INNER JOIN anm2007_families fam ON mem.famid=fam.id Soviel zur Theorie, getestet hab ichs nicht. __________________ Gruss L

01.10.2007, 23:21
Slava Erfahrener Benutzer Registriert seit: 17.01.2006 Beiträge: 468	nicht getestet Code: SELECT fam.id, fam.lastname, mem.surname, mem.birthday, ELT(FIELD(mem.lodgingid,1,2,3,4,5,6),0,20,20,20,33,0) AS LODGINGID, mem.mealid, fam.participationfee * 80 / (select count(*) from anm2007_family_members where famid=fam.id) as beitrag INNER JOIN anm2007_families fam ON mem.famid=fam.id FROM anm2007_family_members mem INNER JOIN anm2007_families fam ON mem.famid=fam.id Edit: nee man muss noch der fall abfangen, wenn mem.lodgingid nicht im wertbereich von 1 bis 6 liegt. also noch eine if() muss rein. oder so wie es vorgeschlagen wurde CASE WHEN mem.lodgingid = 1 OR mem.lodgingid = 6 THEN 0 WHEN mem.lodgingid = 5 THEN 33 WHEN mem.lodgingid in(2,3,4) THEN 20 ELSE mem.lodgingid END CASE __________________ Slava http://bituniverse.com

04.10.2007, 21:42
maeck Erfahrener Benutzer Registriert seit: 28.06.2008 Beiträge: 259	Ihr seid echt super, vielen Dank! Werde es testen und mich dann nochmal melden. Schöne Grüße, Marcel

09.10.2007, 23:22
maeck Erfahrener Benutzer Registriert seit: 28.06.2008 Beiträge: 259	Nach einigem rumprobieren, hat es mit eurer Hilfe geklappt. Vielen Dank nochmal!