# mysql Abfrage | zwei Spalten vergleichen und dann aufsummieren



## kerkow (21. September 2011)

ich brauche bitte Hilfe bei folgendem:

Tabelle
ID | Name | Nummer | Stck
1  | Alpha  |               | 1
1  | Alpha  | 3128      | 1
1  | Alpha  | 3128      | 1
1  |            | 3128      | 1

Hieraus würde ich gerne wissen, wie oft ein Name bzw. Auftragsnummer in der Tabelle vorkommt.
Also ein COUNT auf Name und einer auf Nummer. Klappt, bei beiden bekomme ich drei heraus. Wenn ich jetzt ein verschachteltes SELECT mache, dann nimmt er nur das erste SELECT an und gibt mir als Ergebnis die 3 Namen z.B. aus.

Wie kann ich es anstellen, dass ich hier als Ergebnis 4 bekomme?


----------



## Yaslaw (21. September 2011)

Zeig mal dein verschachteltes SQL.


----------



## kerkow (21. September 2011)

SELECT ort,name,nummer,temp.kn AS stck,temp.ks AS preis FROM (
							  SELECT * FROM
							    (SELECT *,COUNT(name) AS kn,SUM(filpreis) AS ks FROM x_daten WHERE name!='' GROUP BY name) AS temp_name,
							    (SELECT * FROM x_daten WHERE nummer!='' GROUP BY nummer) AS temp_nummer
							  ) AS temp WHERE temp.kn > 2 GROUP BY nummer,name


----------



## Yaslaw (21. September 2011)

*Bitte, bitte den Code in Zukunft in [code=sql]mein Code[/code] setzen*

Das sieht dann ungefähr so aus und somit auch lesbarer

```
SELECT
	ort,
	name,
	nummer,
	temp.kn AS stck,
	temp.ks AS preis 
FROM
	(
		SELECT 
			* 
		FROM
			(
				SELECT 
					*,
					COUNT(name) AS kn,
					SUM(filpreis) AS ks 
				FROM 
					x_daten 
				WHERE
					name!='' 
				GROUP BY name
			) AS temp_name,
			(
				SELECT 
					* 
				FROM 
					x_daten
				WHERE
					nummer!='' 
				GROUP BY nummer
			) AS temp_nummer
	) AS temp 
WHERE 
	temp.kn > 2 
GROUP BY
	nummer,
	name
```
*item: *Du verbindest da 2 Subqueries miteinander ohne JOIN oder WHERE-Verknüpfung. temp_name und temp_nummer. Ist das gewollt, das jede Name mit jeder Nummer ausgegeben wird?

*item: *Du hast im Subquery temp_name ein GROUP BY auf den Namen, gibts aber wegen dem * Alle Felder aus. Somit gruppiert er im Endeffekt auf alle Felder und ncith nur auf den Namen. Ich weiss zwar nichtm, wass dieses Query bewirken soll und was dein Ziel ist, jedoch mach das wenig Sinn. ggf. ist sowas dort besser (gilt übrigens auch für temp_nummer)

```
(
				SELECT 
					name,
					COUNT(name) AS kn,
					SUM(filpreis) AS ks 
				FROM 
					x_daten 
				WHERE
					name!='' 
				GROUP BY name
			) AS temp_name,
```

Am besten schreibst du mal, was das Endresultat sein Soll. Inkl. einem Beispiel. Dann kann ich dir nach meinem Mittagessen weiterhelfen


----------



## kerkow (21. September 2011)

SUPER!

Kunden-Aufträge können eine Auftrags-Nummer oder einen Kommissions-Namen haben, oder im Idealfall beides. Jetzt möchte ich alle Varianten zu einem Kunden auflisten.
Also Kunde KURT hat die Auftragsnummer 1234. Jetzt gibt es den Idealfall zweimal "KURT1234", kommt bei mir in zwei Spalten, einmal der Name in Name und die Nummer in die Auftragsnummer.
Dann kommt ein Eintrag nur mit dem Namen KURT und einmal nur mit der Auftragsnummer 1234. Jetzt habe ich in der DB 4 Einträge, die alle zusammen gehören.
Nun möchte ich wissen, wie oft der Kunde KURT unter der Auftragsnummer 1234 Zeilen in der DB hat = 4.
Klar meine Beschreibung, oder fehlt noch eine Info?


----------



## Yaslaw (21. September 2011)

Vergessen wir jetzt mal das bestehende Query.
So wie ich dich verstanden habe, hast du die Tabelle xdaten. Darin sind bis zu ?vier kombinationen per Kunde  möglich:

```
[xdaten]

kunde  | nr   | km_name
-----------------------
Kurt   | 1234 | kommission1
Kurt   | 1234 | 
Kurt   |      | kommission1
Kurt   |      |
```

Und nun willst du wissen, ob für Kurt diese Vier Varianten da sind?

Mach mir bitte ein Datenbeispiel mit Tabellen. So in der Art:


> Das hab ich:
> 
> ```
> Tabelle mitarbeiter
> ...


----------



## kerkow (21. September 2011)

sorry, werde mein bestes geben:

Tabelle XDATEN
id | ort | kommission | auftragsnummer | stck
----------------------------------------------------
1 | stuttgart | kurt | 1234 | stck
2 | stuttgart | kurt | | stck
3 | stuttgart | | 1234 | stck
4 | muenchen | egon | 567 | stck
5 | muenchen | | 567 | stck
6 | muenchen | egon | | stck
7 | hamburg | fritz | | stck
8 | hamburg | fritz | 890 | stck

ich will wissen, wo mehr als 2 gleiche Aufträge pro Ort vorhanden sind, dort die Stückzahlen aufsummieren:
ort | stueck
--------------------
stuttgart | 3
muenchen | 3
hamburg | 2

also das Ergebnis ist dann:
ort | stueck
--------------------
stuttgart | 3
muenchen | 3


----------



## Yaslaw (21. September 2011)

Sehe ich das richtig, das folgende währen dann 2 versch. Aufträge auf denselben Ort

```
1 | stuttgart | kurt | 1234 | stck
2 | stuttgart | kurt | | stck
3 | stuttgart | | 1234 | stck
10 | stuttgart | hans | 456
```


----------



## kerkow (21. September 2011)

genau, 2 verschiedene aufträge, einer kommt aber 3 mal vor, der andere nur einmal, also will ich ersteren aufsummiert haben (kommen dann noch wertespalten etc. dazu)


----------



## Yaslaw (21. September 2011)

Stellt sich nur noch die Frage. Hast du immer einmal die Kombination (kommission | auftragsnummer ) ?
Und sind diese Werte eindeutig oder kann zum Beispiel 'kurt' in mehreren Orten vorkommen?

Wennd u die erste Frage mti ja und die zweite mit Nein beantworten kannst, dann ist das folgende eine mögliche Lösung für MySQL

```
SELECT
	ort,
	lkomm,
	lauftr,
	COUNT(*) AS anzahl,
	SUM(stck) AS summe
FROM
	(
		SELECT
			@last_komm:= IFNULL(x.kommission, @last_komm) AS lkomm,
			@last_auftr:= IFNULL(x.auftragsnummer, @last_auftr) AS lauftr,
			@last_ort := x.ort AS ort,
			x.stck
		FROM
			xdaten AS x,
			(SELECT @last_ort := '', @last_komm := '', @last_auftr := '') AS vars
		ORDER BY
			x.ort,
			x.kommission DESC,
			x.auftragsnummer DESC
	) AS groups
GROUP BY
	ort,
	lkomm,
	lauftr
HAVING
	COUNT(*) > 2
```

Ergibt dann für dein Beispiel (achtung, Summe ist nicht aufgrund deiner Daten!)

```
ort       | lkomm | lauftr | anzahl | summe
-------------------------------------------
muenchen  | egon  | 567    | 3      | 34
stuttgart | kurt  | 1234   | 3      | 48
```


----------



## kerkow (21. September 2011)

zur ersten Frage: JA
zur zweiten Frage: Eindeutig = JA

Jetzt bekomme ich nur einen ort heraus mit der summe.


----------



## Yaslaw (22. September 2011)

Wie meinst du das 'mit der summe' ?

Ich habe mir aufgrund deines Beispiels eine Tabelle gemacht und das SQL getestet. Natürlich musste ich die Werte im Feld stck selber ausdenken, da du dort kein Wert eingetragen hast.

Das Resultat von meinem Test siehst du oben.

So sieht meine Testtabelle aus. Diese habe ich aufgrund deiner Angaben zusammengebaut.......

```
id | ort       | kommission | auftragsnummer | stck
---------------------------------------------------
1  | stuttgart | kurt       | 1234           | 40
2  | stuttgart | kurt       | NULL           | 1
3  | stuttgart | NULL       | 1234           | 7
4  | muenchen  | egon       | 567            | 10
5  | muenchen  | NULL       | 567            | 12
6  | muenchen  | egon       | NULL           | 12
7  | hamburg   | fritz      | NULL           | 5
8  | hamburg   | fritz      | 890            | 14
```



```
CREATE TABLE `xdaten` (
	`id` INT(10) NULL DEFAULT NULL,
	`ort` VARCHAR(50) NULL DEFAULT NULL COLLATE 'latin1_general_ci',
	`kommission` VARCHAR(50) NULL DEFAULT NULL COLLATE 'latin1_general_ci',
	`auftragsnummer` INT(10) NULL DEFAULT NULL,
	`stck` INT(10) NULL DEFAULT NULL
);
INSERT INTO `xdaten` (`id`, `ort`, `kommission`, `auftragsnummer`, `stck`) VALUES (1, 'stuttgart', 'kurt', 1234, 40);
INSERT INTO `xdaten` (`id`, `ort`, `kommission`, `auftragsnummer`, `stck`) VALUES (2, 'stuttgart', 'kurt', NULL, 1);
INSERT INTO `xdaten` (`id`, `ort`, `kommission`, `auftragsnummer`, `stck`) VALUES (3, 'stuttgart', NULL, 1234, 7);
INSERT INTO `xdaten` (`id`, `ort`, `kommission`, `auftragsnummer`, `stck`) VALUES (4, 'muenchen', 'egon', 567, 10);
INSERT INTO `xdaten` (`id`, `ort`, `kommission`, `auftragsnummer`, `stck`) VALUES (5, 'muenchen', NULL, 567, 12);
INSERT INTO `xdaten` (`id`, `ort`, `kommission`, `auftragsnummer`, `stck`) VALUES (6, 'muenchen', 'egon', NULL, 12);
INSERT INTO `xdaten` (`id`, `ort`, `kommission`, `auftragsnummer`, `stck`) VALUES (7, 'hamburg', 'fritz', NULL, 5);
INSERT INTO `xdaten` (`id`, `ort`, `kommission`, `auftragsnummer`, `stck`) VALUES (8, 'hamburg', 'fritz', 890, 14);
```


----------



## kerkow (22. September 2011)

Danke, dass Du mir hilfst!

Um bei meinem Beispiel #7 zu bleiben:

- bei stck steht immer eine 1 drin, sorry, hab das oben in der Tabelle falsch gemacht.
- ich habe deinen codeschnipsel genutzt und bei mir eingefügt, da bekomme ich aber nur stuttgart angezeigt und nicht noch zusätzlich münchen, vielleicht hat das etwas mit dem last zu tun?
- bin jetzt ratlos


----------



## Yaslaw (22. September 2011)

Wie testest du? Mit welchem Tool?
Ist die DB lokal auf deinem PC? Wenn ja, empfehle ich dir HeidiSQL (http://www.heidisql.com/) zum mit SQL herumzuspielen.

Welches last meinst du? die @last_xy? Das sind nur Variablen. Damit hat es sicher nix zu tun.


----------



## kerkow (22. September 2011)

ich teste mit mysql workbench/administrator
ich habe den Eindruck, dass er nur den letzten Ort auflistet und alle Mehrfachanzahlen darin dann summiert. Deswegen komme ich auf den Verdacht dass es etwas mit dem "last" Ort zu tun hat.

ich hab es jetzt so:

```
$query_x=("
	SELECT
	    ort,lname,lauftr,COUNT(*) AS anzahl,SUM(preis) AS summe
	FROM
	    (
	        SELECT
	            @last_name:= IFNULL(sd.kom_name, @last_name) AS lname,
	            @last_auftr:= IFNULL(sd.kom_auftr, @last_auftr) AS lauftr,
	            @last_ort := sd.ort AS ort,
	            sd.preis
	        FROM
	            x_daten AS sd,
	            (SELECT @last_ort := '', @last_name := '', @last_auftr := '') AS vars
	        ORDER BY
	            sd.ort,
	            sd.kom_name DESC,
	            sd.kom_auftr DESC
	    ) AS groups
	GROUP BY
	    ort,
	    lname,
	    lauftr
	HAVING
	    COUNT(*) > 2
");
$result_x=mysql_query($query_x);
while($row_x=mysql_fetch_object($result_x)) {
	echo "<tr>
			<td>$row_x->ort</td>
			<td class=\"right\">".number_format($row_x->anzahl,0,",",".")."</td>
			<td class=\"right\">".number_format($row_x->summe,2,",",".")."</td>
		</tr>";
}
```


----------



## kerkow (22. September 2011)

wie stelle ich den code anders dar?


----------



## Yaslaw (22. September 2011)

Yaslaw hat gesagt.:


> *Bitte, bitte den Code in Zukunft in [code=sql]mein Code[/code] setzen*
> 
> Das sieht dann ungefähr so aus und somit auch lesbarer
> ...



Für PHP natürlich [PHP] verwenden


----------



## kerkow (22. September 2011)

oh sorry, jetzt hab ich den fehler - es gitb auch komplett leere einträge, die dürfen generell nicht mitgezählt werden. er listet mir jetzt mit deinem script nur die auf, die komplett leer sind.
wie mache ich das jetzt?


----------



## Yaslaw (22. September 2011)

Mein Script listet doch nicht nur leere Einträge auf. Was sind den bei dir leere Einträge? Eine Zeile ohne etwas drin?

Sag mir doch mal, wie ich etwas programmieren und Testen soll, wenn ich die Daten nicht oder wenn dann falsch habe? Auf diese Weise werde ich nie deine Probleme nachvollziehen können


----------



## kerkow (22. September 2011)

Das sind alle Daten, Du hast das auch richtig gemacht. Allerdings gibt es ein paar Einträge in der DB (die ich vergessen habe zu erwähnen), wo der name und die nummer leer sind. Also weder Kurt o.ä. noch 1234 o.ä.


----------



## Yaslaw (22. September 2011)

Und was soll mit denen geschehen?


----------



## kerkow (22. September 2011)

die möchte ich nicht mitgezählt bekommen - also ignorieren.
Ich habe das script von dir kopiert, wie du an meinem codeschnipsel siehst und dann listet er nur die orte, die leere namen und leere auftragsnummern haben. die werden auch korrekt aufsummiert.


----------



## Yaslaw (22. September 2011)

Ich habe jetzt in meinen Beispieldaten noch eine Zeile mit Stuttgart ohne Nummer und ohne Namen erfasst.
Dann hab ich noche in WHERE eingebaut um diesen rauszufiltern. Das Resulat sind wieder die beiden Wie oben

```
SELECT
	ort,
	lkomm,
	lauftr,
	COUNT(*) AS anzahl,
	SUM(preis) AS summe
FROM
	(
		SELECT
			@last_komm:= IFNULL(x.kom_name, @last_komm) AS lkomm,
			@last_auftr:= IFNULL(x.kom_auftr, @last_auftr) AS lauftr,
			@last_ort := x.ort AS ort,
			x.preis
		FROM
			xdaten AS x,
			(SELECT @last_ort := '', @last_komm := '', @last_auftr := '') AS vars
		WHERE
			NOT (x.kom_name IS NULL AND x.kom_auftr IS NULL)
		ORDER BY
			x.ort,
			x.kom_name DESC,
			x.kom_auftr DESC
	) AS groups
GROUP BY
	ort,
	lkomm,
	lauftr
HAVING
	COUNT(*) > 2
```

Und wenn ich dein PHP laufen lasse, bekomme ich 
muenchen 3 3,00 stuttgart 4 4,00 

Was du ggf. mal noch ausprobieren kannst, ist im Subselect wo die Variabeln gesetzt werden die Leerstrings durch 0 Ersetzen

```
(SELECT @last_ort := 0, @last_komm := 0, @last_auftr := 0) AS vars
```


----------



## kerkow (22. September 2011)

jetzt habe ich mal den mysql-editor gewechselt und siehe da:

illegal mix of collations ... for operation 'ifnull'

wo muss ich jetzt ein COLLATE einfügen?


----------



## Yaslaw (22. September 2011)

Hast du es so versucht?

```
(SELECT @last_ort := 0, @last_komm := 0, @last_auftr := 0) AS vars
```


----------



## kerkow (22. September 2011)

kommt diesselbe Aussage :-(


----------



## Yaslaw (22. September 2011)

Was steht dort bei deinen .... ? Welche werden denn verwendet?


----------



## kerkow (22. September 2011)

Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (binary,NONE) for operation 'ifnull'

eine Ausgabe kommt damit natürlich nicht


----------



## kerkow (22. September 2011)

jetzt habe ich noch weiter im Browser getestet:
1. Wenn ich die Nullen statt '' einfüge, dann kommt die "illegal mix" Fehlermeldung.
2. Wenn ich die '' statt den Nullen einfüge, dann kommt das Ergebnis, korrekt zusammen gezählt und korrekt summiert, allerdings nur die zeilen, die im kom_name und kom_auftr leer sind


----------

