Pfthb

My aim here is to get the repairNo if the count value is equals to 1 else returns a default value as null or something. Here before applying the case statement works fine but after using case, not returns as expected. Returns each item as a single row. No group by applied. How to solve this. Any suggestions welcome.

SELECT kunden.kundennr, 
 kunden.firma, 
 kunden.vorname, 
 kunden.nachname, 
 kunden.mobil, 
 kunden.email, 
 kunden.geburtsdatum, 
 kunden.isdatenschutzerklaerung, 
 Count(reparaturen.reparaturnr) AS Counts, 
 CASE 
 WHEN Count(reparaturen.reparaturnr) = 1 THEN Cast( 
 reparaturen.reparaturnr AS NVARCHAR(20)) 
 ELSE 0 
 END AS RepairNo 
FROM kunden 
 LEFT JOIN reparaturen 
 ON reparaturen.kundennr = kunden.kundennr 
 LEFT JOIN personal AS PersonalAngenommen 
 ON reparaturen.personalnr = PersonalAngenommen.personalnr 
 LEFT JOIN lieferanten 
 ON reparaturen.kennnr = lieferanten.lieferantennr 
 AND reparaturen.kenntyp = 2 
 LEFT JOIN personal 
 ON reparaturen.kennnr = personal.personalnr 
 AND reparaturen.kenntyp = 1 
WHERE kunden.geloescht = 0 
 AND ( kunden.firma LIKE '%G%' ) 
GROUP BY kunden.kundennr, 
 kunden.firma, 
 kunden.vorname, 
 kunden.nachname, 
 kunden.mobil, 
 kunden.email, 
 kunden.geburtsdatum, 
 kunden.isdatenschutzerklaerung, 
 reparaturen.reparaturnr 
ORDER BY kunden.nachname, 
 kunden.vorname 

You can not have Reparaturen.ReparaturNr in the CASE because you don't GROUP BY it.

(Follows on from the comment that correctly tells you to remove it from the GROUP BY.)

After removing it from the GROUP BY, then try...

 CASE 
 WHEN Count(reparaturen.reparaturnr) = 1 THEN Cast( 
 MAX(reparaturen.reparaturnr) AS NVARCHAR(20)) 
 ELSE 0 
 END AS RepairNo

Then you're using an aggregate function, like the error message tells you to.

SELECT kunden.kundennr, 
 kunden.firma, 
 kunden.vorname, 
 kunden.nachname, 
 kunden.mobil, 
 kunden.email, 
 kunden.geburtsdatum, 
 kunden.isdatenschutzerklaerung, 
 Count(reparaturen.reparaturnr) AS Counts, 
 CASE 
 WHEN Count(reparaturen.reparaturnr) = 1 THEN Cast( 
 MAX(reparaturen.reparaturnr) AS NVARCHAR(20)) 
 ELSE 0 
 END AS RepairNo 
FROM kunden 
 LEFT JOIN reparaturen 
 ON reparaturen.kundennr = kunden.kundennr 
 LEFT JOIN personal AS PersonalAngenommen 
 ON reparaturen.personalnr = PersonalAngenommen.personalnr 
 LEFT JOIN lieferanten 
 ON reparaturen.kennnr = lieferanten.lieferantennr 
 AND reparaturen.kenntyp = 2 
 LEFT JOIN personal 
 ON reparaturen.kennnr = personal.personalnr 
 AND reparaturen.kenntyp = 1 
WHERE kunden.geloescht = 0 
 AND ( kunden.firma LIKE '%G%' ) 
GROUP BY kunden.kundennr, 
 kunden.firma, 
 kunden.vorname, 
 kunden.nachname, 
 kunden.mobil, 
 kunden.email, 
 kunden.geburtsdatum, 
 kunden.isdatenschutzerklaerung
ORDER BY kunden.nachname, 
 kunden.vorname 

You will need to calculate repairno in a different query. Please try:

;with cte as (

SELECT
Kunden.KundenNr
, Kunden.Firma
, Kunden.Vorname
, Kunden.Nachname
, Kunden.Mobil
, Kunden.EMail
, Kunden.Geburtsdatum
, Kunden.IsDatenschutzerklaerung
,COUNT(Reparaturen.ReparaturNr) as Counts
FROM Kunden
 LEFT JOIN Reparaturen on Reparaturen.KundenNr = Kunden.KundenNr 
 LEFT JOIN Personal AS PersonalAngenommen ON Reparaturen.PersonalNr = PersonalAngenommen.PersonalNr 
 LEFT JOIN Lieferanten ON Reparaturen.KennNr = Lieferanten.LieferantenNr AND Reparaturen.KennTyp = 2
 LEFT JOIN Personal ON Reparaturen.KennNr = Personal.PersonalNr AND Reparaturen.KennTyp = 1
WHERE Kunden.Geloescht = 0
AND (
Kunden.Firma Like '%G%'
)GROUP BY Kunden.KundenNr,Kunden.Firma, Kunden.Vorname
 , Kunden.Nachname 
 , Kunden.Mobil 
 , Kunden.EMail 
 , Kunden.Geburtsdatum 
 , Kunden.IsDatenschutzerklaerung


)
select *, 
 case when Counts = 1 Then cast(r.reparaturnr as nvarchar(20)) else 0 end as RepairNo
from cte
LEFT JOIN reparaturen r
ON r.kundenNr = cte.kundenNr

You also need to watch out when using GROUP BY and a filter like where. When you are using the GROUP BY you should use the HAVING clause. Using the where can cause unwanted anomalies.

a blind guess would be that you would need to have the 0 as a text by using single quotes

case when COUNT(Reparaturen.ReparaturNr) = 1 Then cast(Reparaturen.ReparaturNr as nvarchar(20)) else '0' end as RepairNo