GROUP BY not working when applying case in SQL Server










0















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









share|improve this question
























  • What is the error message, if any, or can you show us some sample data which explains your problem?

    – Tim Biegeleisen
    Nov 12 '18 at 13:52











  • The group-by is not working bro.The rows are not grouped if 10 rows means those all are displayed in a separate row. But I need the Total count as 10, and if it's one means it's corresponding RepairNo.

    – SPYder
    Nov 12 '18 at 13:55











  • The value for one column is always different, like ID type. Omit this column from your select and groupby statements, u will be fine.

    – Eray Balkanli
    Nov 12 '18 at 13:57






  • 2





    Remove Reparaturen.ReparaturNr from the GROUP BY clause.

    – jarlh
    Nov 12 '18 at 13:57











  • After I remove got error 'Column 'Reparaturen.ReparaturNr' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.'

    – SPYder
    Nov 12 '18 at 14:00















0















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









share|improve this question
























  • What is the error message, if any, or can you show us some sample data which explains your problem?

    – Tim Biegeleisen
    Nov 12 '18 at 13:52











  • The group-by is not working bro.The rows are not grouped if 10 rows means those all are displayed in a separate row. But I need the Total count as 10, and if it's one means it's corresponding RepairNo.

    – SPYder
    Nov 12 '18 at 13:55











  • The value for one column is always different, like ID type. Omit this column from your select and groupby statements, u will be fine.

    – Eray Balkanli
    Nov 12 '18 at 13:57






  • 2





    Remove Reparaturen.ReparaturNr from the GROUP BY clause.

    – jarlh
    Nov 12 '18 at 13:57











  • After I remove got error 'Column 'Reparaturen.ReparaturNr' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.'

    – SPYder
    Nov 12 '18 at 14:00













0












0








0








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









share|improve this question
















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






sql sql-server group-by






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Nov 12 '18 at 14:05









octano

208415




208415










asked Nov 12 '18 at 13:49









SPYderSPYder

237




237












  • What is the error message, if any, or can you show us some sample data which explains your problem?

    – Tim Biegeleisen
    Nov 12 '18 at 13:52











  • The group-by is not working bro.The rows are not grouped if 10 rows means those all are displayed in a separate row. But I need the Total count as 10, and if it's one means it's corresponding RepairNo.

    – SPYder
    Nov 12 '18 at 13:55











  • The value for one column is always different, like ID type. Omit this column from your select and groupby statements, u will be fine.

    – Eray Balkanli
    Nov 12 '18 at 13:57






  • 2





    Remove Reparaturen.ReparaturNr from the GROUP BY clause.

    – jarlh
    Nov 12 '18 at 13:57











  • After I remove got error 'Column 'Reparaturen.ReparaturNr' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.'

    – SPYder
    Nov 12 '18 at 14:00

















  • What is the error message, if any, or can you show us some sample data which explains your problem?

    – Tim Biegeleisen
    Nov 12 '18 at 13:52











  • The group-by is not working bro.The rows are not grouped if 10 rows means those all are displayed in a separate row. But I need the Total count as 10, and if it's one means it's corresponding RepairNo.

    – SPYder
    Nov 12 '18 at 13:55











  • The value for one column is always different, like ID type. Omit this column from your select and groupby statements, u will be fine.

    – Eray Balkanli
    Nov 12 '18 at 13:57






  • 2





    Remove Reparaturen.ReparaturNr from the GROUP BY clause.

    – jarlh
    Nov 12 '18 at 13:57











  • After I remove got error 'Column 'Reparaturen.ReparaturNr' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.'

    – SPYder
    Nov 12 '18 at 14:00
















What is the error message, if any, or can you show us some sample data which explains your problem?

– Tim Biegeleisen
Nov 12 '18 at 13:52





What is the error message, if any, or can you show us some sample data which explains your problem?

– Tim Biegeleisen
Nov 12 '18 at 13:52













The group-by is not working bro.The rows are not grouped if 10 rows means those all are displayed in a separate row. But I need the Total count as 10, and if it's one means it's corresponding RepairNo.

– SPYder
Nov 12 '18 at 13:55





The group-by is not working bro.The rows are not grouped if 10 rows means those all are displayed in a separate row. But I need the Total count as 10, and if it's one means it's corresponding RepairNo.

– SPYder
Nov 12 '18 at 13:55













The value for one column is always different, like ID type. Omit this column from your select and groupby statements, u will be fine.

– Eray Balkanli
Nov 12 '18 at 13:57





The value for one column is always different, like ID type. Omit this column from your select and groupby statements, u will be fine.

– Eray Balkanli
Nov 12 '18 at 13:57




2




2





Remove Reparaturen.ReparaturNr from the GROUP BY clause.

– jarlh
Nov 12 '18 at 13:57





Remove Reparaturen.ReparaturNr from the GROUP BY clause.

– jarlh
Nov 12 '18 at 13:57













After I remove got error 'Column 'Reparaturen.ReparaturNr' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.'

– SPYder
Nov 12 '18 at 14:00





After I remove got error 'Column 'Reparaturen.ReparaturNr' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.'

– SPYder
Nov 12 '18 at 14:00












4 Answers
4






active

oldest

votes


















1














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





share|improve this answer

























  • @SPYder - Have you tried this yet?

    – MatBailie
    Nov 12 '18 at 14:48



















0














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





share|improve this answer

























  • Here I'm getting the count again from Cte result but I need it's corrosponding ReparaturNr. How do I get this?

    – SPYder
    Nov 12 '18 at 14:09











  • The op is casting a column to a varchar, you're casting the count of that column to a varchar. That's not remotely the same.

    – MatBailie
    Nov 12 '18 at 14:10











  • @MatBailie which column the OP converts into varchar, I can't see it. I only see nvarchar.

    – Eray Balkanli
    Nov 12 '18 at 14:10











  • @SPYder the result u get from cte is the corrosponding reparaturNr. Count is calculated based on reparaturNr

    – Eray Balkanli
    Nov 12 '18 at 14:11











  • Don't be pedantic. NVARCHAR, VARCHAR, NVARCHAR(20), whatever. The OP has CAST(Reparaturen.ReparaturNr AS NVARCHAR(20)), but you have CAST(COUNT(Reparaturen.ReparaturNr) AS NVARCHAR(20))

    – MatBailie
    Nov 12 '18 at 14:13


















0














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.






share|improve this answer























  • This is untrue. There is nothing stopping you using WHERE x=1 GROUP BY y HAVING MAX(Y) = 2. I use WHERE with GROUP BY a lot more often than I use HAVING.

    – MatBailie
    Nov 13 '18 at 8:15



















-1














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





share|improve this answer























  • It's also not working bro..Any other solutions to fix this?

    – SPYder
    Nov 12 '18 at 14:01










Your Answer






StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);













draft saved

draft discarded


















StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53263578%2fgroup-by-not-working-when-applying-case-in-sql-server%23new-answer', 'question_page');

);

Post as a guest















Required, but never shown

























4 Answers
4






active

oldest

votes








4 Answers
4






active

oldest

votes









active

oldest

votes






active

oldest

votes









1














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





share|improve this answer

























  • @SPYder - Have you tried this yet?

    – MatBailie
    Nov 12 '18 at 14:48
















1














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





share|improve this answer

























  • @SPYder - Have you tried this yet?

    – MatBailie
    Nov 12 '18 at 14:48














1












1








1







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





share|improve this answer















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






share|improve this answer














share|improve this answer



share|improve this answer








edited Nov 12 '18 at 14:22

























answered Nov 12 '18 at 14:14









MatBailieMatBailie

59.2k1475110




59.2k1475110












  • @SPYder - Have you tried this yet?

    – MatBailie
    Nov 12 '18 at 14:48


















  • @SPYder - Have you tried this yet?

    – MatBailie
    Nov 12 '18 at 14:48

















@SPYder - Have you tried this yet?

– MatBailie
Nov 12 '18 at 14:48






@SPYder - Have you tried this yet?

– MatBailie
Nov 12 '18 at 14:48














0














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





share|improve this answer

























  • Here I'm getting the count again from Cte result but I need it's corrosponding ReparaturNr. How do I get this?

    – SPYder
    Nov 12 '18 at 14:09











  • The op is casting a column to a varchar, you're casting the count of that column to a varchar. That's not remotely the same.

    – MatBailie
    Nov 12 '18 at 14:10











  • @MatBailie which column the OP converts into varchar, I can't see it. I only see nvarchar.

    – Eray Balkanli
    Nov 12 '18 at 14:10











  • @SPYder the result u get from cte is the corrosponding reparaturNr. Count is calculated based on reparaturNr

    – Eray Balkanli
    Nov 12 '18 at 14:11











  • Don't be pedantic. NVARCHAR, VARCHAR, NVARCHAR(20), whatever. The OP has CAST(Reparaturen.ReparaturNr AS NVARCHAR(20)), but you have CAST(COUNT(Reparaturen.ReparaturNr) AS NVARCHAR(20))

    – MatBailie
    Nov 12 '18 at 14:13















0














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





share|improve this answer

























  • Here I'm getting the count again from Cte result but I need it's corrosponding ReparaturNr. How do I get this?

    – SPYder
    Nov 12 '18 at 14:09











  • The op is casting a column to a varchar, you're casting the count of that column to a varchar. That's not remotely the same.

    – MatBailie
    Nov 12 '18 at 14:10











  • @MatBailie which column the OP converts into varchar, I can't see it. I only see nvarchar.

    – Eray Balkanli
    Nov 12 '18 at 14:10











  • @SPYder the result u get from cte is the corrosponding reparaturNr. Count is calculated based on reparaturNr

    – Eray Balkanli
    Nov 12 '18 at 14:11











  • Don't be pedantic. NVARCHAR, VARCHAR, NVARCHAR(20), whatever. The OP has CAST(Reparaturen.ReparaturNr AS NVARCHAR(20)), but you have CAST(COUNT(Reparaturen.ReparaturNr) AS NVARCHAR(20))

    – MatBailie
    Nov 12 '18 at 14:13













0












0








0







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





share|improve this answer















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






share|improve this answer














share|improve this answer



share|improve this answer








edited Nov 12 '18 at 14:15

























answered Nov 12 '18 at 14:02









Eray BalkanliEray Balkanli

4,13242044




4,13242044












  • Here I'm getting the count again from Cte result but I need it's corrosponding ReparaturNr. How do I get this?

    – SPYder
    Nov 12 '18 at 14:09











  • The op is casting a column to a varchar, you're casting the count of that column to a varchar. That's not remotely the same.

    – MatBailie
    Nov 12 '18 at 14:10











  • @MatBailie which column the OP converts into varchar, I can't see it. I only see nvarchar.

    – Eray Balkanli
    Nov 12 '18 at 14:10











  • @SPYder the result u get from cte is the corrosponding reparaturNr. Count is calculated based on reparaturNr

    – Eray Balkanli
    Nov 12 '18 at 14:11











  • Don't be pedantic. NVARCHAR, VARCHAR, NVARCHAR(20), whatever. The OP has CAST(Reparaturen.ReparaturNr AS NVARCHAR(20)), but you have CAST(COUNT(Reparaturen.ReparaturNr) AS NVARCHAR(20))

    – MatBailie
    Nov 12 '18 at 14:13

















  • Here I'm getting the count again from Cte result but I need it's corrosponding ReparaturNr. How do I get this?

    – SPYder
    Nov 12 '18 at 14:09











  • The op is casting a column to a varchar, you're casting the count of that column to a varchar. That's not remotely the same.

    – MatBailie
    Nov 12 '18 at 14:10











  • @MatBailie which column the OP converts into varchar, I can't see it. I only see nvarchar.

    – Eray Balkanli
    Nov 12 '18 at 14:10











  • @SPYder the result u get from cte is the corrosponding reparaturNr. Count is calculated based on reparaturNr

    – Eray Balkanli
    Nov 12 '18 at 14:11











  • Don't be pedantic. NVARCHAR, VARCHAR, NVARCHAR(20), whatever. The OP has CAST(Reparaturen.ReparaturNr AS NVARCHAR(20)), but you have CAST(COUNT(Reparaturen.ReparaturNr) AS NVARCHAR(20))

    – MatBailie
    Nov 12 '18 at 14:13
















Here I'm getting the count again from Cte result but I need it's corrosponding ReparaturNr. How do I get this?

– SPYder
Nov 12 '18 at 14:09





Here I'm getting the count again from Cte result but I need it's corrosponding ReparaturNr. How do I get this?

– SPYder
Nov 12 '18 at 14:09













The op is casting a column to a varchar, you're casting the count of that column to a varchar. That's not remotely the same.

– MatBailie
Nov 12 '18 at 14:10





The op is casting a column to a varchar, you're casting the count of that column to a varchar. That's not remotely the same.

– MatBailie
Nov 12 '18 at 14:10













@MatBailie which column the OP converts into varchar, I can't see it. I only see nvarchar.

– Eray Balkanli
Nov 12 '18 at 14:10





@MatBailie which column the OP converts into varchar, I can't see it. I only see nvarchar.

– Eray Balkanli
Nov 12 '18 at 14:10













@SPYder the result u get from cte is the corrosponding reparaturNr. Count is calculated based on reparaturNr

– Eray Balkanli
Nov 12 '18 at 14:11





@SPYder the result u get from cte is the corrosponding reparaturNr. Count is calculated based on reparaturNr

– Eray Balkanli
Nov 12 '18 at 14:11













Don't be pedantic. NVARCHAR, VARCHAR, NVARCHAR(20), whatever. The OP has CAST(Reparaturen.ReparaturNr AS NVARCHAR(20)), but you have CAST(COUNT(Reparaturen.ReparaturNr) AS NVARCHAR(20))

– MatBailie
Nov 12 '18 at 14:13





Don't be pedantic. NVARCHAR, VARCHAR, NVARCHAR(20), whatever. The OP has CAST(Reparaturen.ReparaturNr AS NVARCHAR(20)), but you have CAST(COUNT(Reparaturen.ReparaturNr) AS NVARCHAR(20))

– MatBailie
Nov 12 '18 at 14:13











0














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.






share|improve this answer























  • This is untrue. There is nothing stopping you using WHERE x=1 GROUP BY y HAVING MAX(Y) = 2. I use WHERE with GROUP BY a lot more often than I use HAVING.

    – MatBailie
    Nov 13 '18 at 8:15
















0














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.






share|improve this answer























  • This is untrue. There is nothing stopping you using WHERE x=1 GROUP BY y HAVING MAX(Y) = 2. I use WHERE with GROUP BY a lot more often than I use HAVING.

    – MatBailie
    Nov 13 '18 at 8:15














0












0








0







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.






share|improve this answer













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.







share|improve this answer












share|improve this answer



share|improve this answer










answered Nov 12 '18 at 17:35









TedTed

302




302












  • This is untrue. There is nothing stopping you using WHERE x=1 GROUP BY y HAVING MAX(Y) = 2. I use WHERE with GROUP BY a lot more often than I use HAVING.

    – MatBailie
    Nov 13 '18 at 8:15


















  • This is untrue. There is nothing stopping you using WHERE x=1 GROUP BY y HAVING MAX(Y) = 2. I use WHERE with GROUP BY a lot more often than I use HAVING.

    – MatBailie
    Nov 13 '18 at 8:15

















This is untrue. There is nothing stopping you using WHERE x=1 GROUP BY y HAVING MAX(Y) = 2. I use WHERE with GROUP BY a lot more often than I use HAVING.

– MatBailie
Nov 13 '18 at 8:15






This is untrue. There is nothing stopping you using WHERE x=1 GROUP BY y HAVING MAX(Y) = 2. I use WHERE with GROUP BY a lot more often than I use HAVING.

– MatBailie
Nov 13 '18 at 8:15












-1














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





share|improve this answer























  • It's also not working bro..Any other solutions to fix this?

    – SPYder
    Nov 12 '18 at 14:01















-1














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





share|improve this answer























  • It's also not working bro..Any other solutions to fix this?

    – SPYder
    Nov 12 '18 at 14:01













-1












-1








-1







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





share|improve this answer













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






share|improve this answer












share|improve this answer



share|improve this answer










answered Nov 12 '18 at 13:57









George JosephGeorge Joseph

1,44249




1,44249












  • It's also not working bro..Any other solutions to fix this?

    – SPYder
    Nov 12 '18 at 14:01

















  • It's also not working bro..Any other solutions to fix this?

    – SPYder
    Nov 12 '18 at 14:01
















It's also not working bro..Any other solutions to fix this?

– SPYder
Nov 12 '18 at 14:01





It's also not working bro..Any other solutions to fix this?

– SPYder
Nov 12 '18 at 14:01

















draft saved

draft discarded
















































Thanks for contributing an answer to Stack Overflow!


  • Please be sure to answer the question. Provide details and share your research!

But avoid


  • Asking for help, clarification, or responding to other answers.

  • Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.




draft saved


draft discarded














StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53263578%2fgroup-by-not-working-when-applying-case-in-sql-server%23new-answer', 'question_page');

);

Post as a guest















Required, but never shown





















































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown

































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown







Popular posts from this blog

Use pre created SQLite database for Android project in kotlin

Darth Vader #20

Ondo