PDA

Archiv verlassen und diese Seite im Standarddesign anzeigen : SQL Abfrage mit Berechnung und Group By


Gast
2014-07-20, 18:19:01
Hallo, ich habe ein Problem mit folgendem Fall:

http://sqlfiddle.com/#!2/b4f12e/4

Ziel ist es für jede Software die Anzahl der Lizenzen, der aktiven Nutzer sowie der noch freien User Lizenzen anzuzeigen. Zur Berechnung soll dabei die aktuellste Customer License dienen.

In dem Beispiel müsste das Ergbnis so aussehen:

Software Licenses User Free
Windows 150 3 147
Linux 50 1 49

Folgende Fragen habe ich jetzt?
1) der User mit der ID 6 wird nur für Software "Linux" aufgelistet, obwohl er auch die Software "Windows" nutzt (Schuld ist das Group By user.id. Nur wie kann ich das ändern?)
2) Wie summiere ich diese Liste gruppiert nach Software zusammen?
3) Taugt das Query so überhaupt oder gibt es schnellere / effizientere Wege um ans Ziel zu kommen?

Marscel
2014-07-20, 18:59:21
Spontan runtergeschrieben:

SELECT
s.name,
COUNT(us.id) AS 'User',
COALESCE((SELECT licenses FROM customer_licenses WHERE software_id = s.id ORDER BY active_from DESC LIMIT 1), 0) AS 'Licenses',
COALESCE((SELECT licenses FROM customer_licenses WHERE software_id = s.id ORDER BY active_from DESC LIMIT 1), 0) - COUNT(us.id) AS 'Free'
FROM software s
LEFT JOIN user_software us ON us.software_id = s.id
GROUP BY s.id

Gast
2014-07-20, 19:28:33
Funktioniert. Vielen Dank! Mit COALESCE habe ich so noch nie gearbeitet.

Gast
2014-07-21, 12:08:53
Ein Frage hätte ich dann doch noch. Ich benötige in der WHERE Bedingung die customer_id aus der customer_licenses Tabelle. Wenn ich diese Tabelle jetzt allerdings joine werden sämtliche Werte verdoppelt. Wie kann ich das verhindern?

Marscel
2014-07-21, 13:29:04
Versuch in SQL immer erstmal die stumpfeste Methode, das reicht meist schon:

WHERE (SELECT customer_id FROM customer_licenses WHERE software_id = s.id ORDER BY active_from DESC LIMIT 1) = 'C1'

Gast
2014-07-21, 15:33:46
Versuch in SQL immer erstmal die stumpfeste Methode, das reicht meist schon:

WHERE (SELECT customer_id FROM customer_licenses WHERE software_id = s.id ORDER BY active_from DESC LIMIT 1) = 'C1'

Das Problem mit dem Limit im WHERE Subselect ist jetzt nur, dass wenn ein weiterer Customer eine License für z.B Software 2 hat, die Software 2 nicht mehr im im Ergebnis für Customer 1 auftaucht.

http://sqlfiddle.com/#!2/20995/1

Marscel
2014-07-21, 21:10:44
Dann die Customer der Nutzer zusammenketten und in den Subselects ebenfalls den richtigen Customer wählen.

SELECT
s.name,
COUNT(us.id) AS 'User',
COALESCE((SELECT licenses FROM customer_licenses WHERE software_id = s.id AND customer_id = u.customer_id ORDER BY active_from DESC LIMIT 1), 0) AS 'Licenses',
COALESCE((SELECT licenses FROM customer_licenses WHERE software_id = s.id AND customer_id = u.customer_id ORDER BY active_from DESC LIMIT 1), 0) - COUNT(us.id) AS 'Free'
FROM software s
LEFT JOIN user_software us ON us.software_id = s.id
INNER JOIN user u ON us.user_id = u.id
WHERE u.customer_id = 'C1'
GROUP BY s.id

Gast
2014-07-21, 21:56:46
Vielen Dank für deine Antwort. Ich hatte es zwischenzeitlich fast ähnlich gelöst:


SELECT
s.name,
COUNT(us.id) AS 'User',
COALESCE((SELECT licenses
FROM customer_licenses
WHERE software_id = s.id
AND customer_id = 'C1'
ORDER BY active_from DESC
LIMIT 1), 0) AS 'Licenses',
COALESCE((SELECT licenses
FROM customer_licenses
WHERE software_id = s.id
AND customer_id = 'C1'
ORDER BY active_from DESC
LIMIT 1), 0) - COUNT(us.id) AS 'Free'
FROM software s
LEFT JOIN user_software us ON us.software_id = s.id
WHERE (SELECT customer_id
FROM customer_licenses
WHERE software_id = s.id
AND customer_id = 'C1'
ORDER BY active_from DESC
LIMIT 1) = 'C1'
GROUP BY s.id


Deine Methode mit dem Inner Join finde allerdings schöner ;)