SQL Abfrage mit Join und Anzahl

[Daywalker11]

Hallo zusammen,

ich habe noch keine große DB Erfahrung und haben nun die Herausforderung die ich euch gerne Anhand der Tabellen darstellen möchte:

Tabelle 1:
SachNummer; LieferantenNummer; LieferantenIndex; AmpelStatus
S111; L331; A;3
S112; L331; B; 2
S113; L331; B; 2
S114; L332; A; 2
S115; L332; A; 1
S116; L332; A; 0

Die AmpelStati gehen von 0 bis 3

Tabelle2:
LieferantenNummer; Lieferatenindex; Name; Adresse
L331; A; FirmaA; AdresseA
L331; B; FirmaA; AdresseB
L332; A; FirmaB; AdresseBA

Ergebnistabelle:
LieferatenNummer; Lieferantenindex; Name; Adresse; AmpelStatus=0; AmpelStatus =1; Ampelstatus=2; Ampelstatus=3;
L331; A; FirmaA; AdresseA; 0; 0; 0; 0; 1
L331; B; Firma A; AdresseB; 0; 0; 2; 0; 0
L332; A; FirmaB; AdresseBA; 1; 1; 0; 0; 0


In der Zieltabelle sollen also immer die Anzahl der Sachnummern in den einzelnen Ampelstati angegeben werden, welche den einzelnen Lieferanten und deren Standorten zugeordnet werden können.

Wäre super wenn ihr mir weiterhelfen könntet
Besten Dank und Grüße
Chris

 

[akretschmer]

Ohne Kaffee ...

test=*# select * from t1;
  sn  |  ln  | li | status
------+------+----+--------
 s111 | l331 | a  |  3
 s112 | l331 | b  |  2
 s113 | l331 | b  |  2
 s114 | l332 | a  |  2
 s115 | l332 | a  |  1
 s116 | l332 | a  |  0
(6 Zeilen)

test=*# select * from t2;
  ln  | li |  name  |  adresse   
------+----+---------+------------
 l331 | a  | firma a | adresse s
 l331 | b  | firma a | adresse b
 l332 | a  | firma b | adresse ba
(3 Zeilen)

test=*# select t2.ln, t2.li, t2.name, t2.adresse, count(t1.status) filter (where t1.status = 0) as as0, count(t1.status) filter (where t1.status=1) as as1, count(t1.status) filter (where t1.status = 2) as as2, count(t1.status) filter (where t1.status=3) as as3 from t2 left join t1 on t2.ln=t1.ln group by 1,2,3,4 order by 1,2;
  ln  | li |  name  |  adresse  | as0 | as1 | as2 | as3
------+----+---------+------------+-----+-----+-----+-----
 l331 | a  | firma a | adresse s  |  0 |  0 |  2 |  1
 l331 | b  | firma a | adresse b  |  0 |  0 |  2 |  1
 l332 | a  | firma b | adresse ba |  1 |  1 |  1 |  0
(3 Zeilen)

test=*#

 

[ukulele]

filter() kennt MSSQL nicht daher müsstest du es mit CASE machen:

SELECT   t2.ln,
     t2.li,
     t2.name,
     t2.adresse,
     sum(CASE WHEN t1.[status] = 0 THEN 1 ELSE 0 END) AS as0,
     sum(CASE WHEN t1.[status] = 1 THEN 1 ELSE 0 END) AS as1,
     sum(CASE WHEN t1.[status] = 2 THEN 1 ELSE 0 END) AS as2,
     sum(CASE WHEN t1.[status] = 3 THEN 1 ELSE 0 END) AS as3
FROM   t2
LEFT JOIN t1 ON t2.ln = t1.ln
GROUP BY t2.ln,t2.li,t2.name,t2.adresse
ORDER BY 1,2;

 

[Daywalker11]

Hallo Ihr beiden, danke für eure schnelle Hilfe. Die Lösung von Ukulele funktioniert einwandfrei
Mit der filter Funktion hat meine Anwendung leider auch Probleme gehabt... Aber trotzdem interessanter Ansatz.

Grüße aus Stuttgart

 

[akretschmer]

Mit der filter Funktion hat meine Anwendung leider auch Probleme gehabt... Aber trotzdem interessanter Ansatz.

Ja, FILTER ist ein recht neuer SQL-Standard. Kann PostgreSQL und ??? (keine Ahnung, wer noch)