Jump to content

Recommended Posts

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!

 

 

Link to post
Share on other sites

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.

Link to post
Share on other sites

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!

 

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.