Simple JOIN Problem

[gczychi]

Hi,

I have a table Beleg:: that I can query like this

SELECT Beleg.id_Konto, COUNT ( Beleg.id_Konto ) FROM Beleg  WHERE Beleg.Year = 2020  GROUP BY id_Konto

To get the COUNT of each id_Konto in Beleg:

2e974529-5bf6-47e1-b1a7-05b3b2582aea    397
cb54987b-1eec-4868-bcb5-14e2efccc1be    83
ea461038-43f1-43b8-8d68-a2f357f7f41f    27

And I have a table Konto:: that has the names for the id_Konto = Konto.id values:

SELECT Konto.Kontonr , Konto.Name_short FROM Konto
WHERE Konto.id IN (
	SELECT  ( Beleg.id_Konto ) FROM Beleg
	WHERE Beleg.Year = 2020
	)
ORDER BY Konto.Kontonr

The Name_short that I need:

1000	Kasse
1210	Bank1
1240	Bank4

However, I can’t bring them together!  I just can’t find the correct syntax to produce this:

1000	Kasse	397
1210    Bank1	83
1240    Bank4	27

 

Can someone please help?

Thanks!

 

 

[comment]

Try:

SELECT Konto.Kontonr, Konto.Name_short, COUNT ( Beleg.id_Konto ) 
FROM Beleg
INNER JOIN Konto ON Konto.id = Beleg.id_Konto 
WHERE Beleg.Year = 2020
GROUP BY Konto.Kontonr, Konto.Name_short

Untested, because we have no file to test against.

[gczychi]

GREAT, works like a charm!

I tried everything, and the only thing that I missed was the second field name ", Konto.Name_short" in the GROUP BY line. I kept getting the error message — and now I know that THIS error message was actually saying where the problem was… 😀

All non-aggregated column references in the SELECT list and HAVING clause must be in the GROUP BY clause.

Thanks a lot!