sum + name (1 Viewer)

georg7

Registered User.
Local time
Today, 07:59
Joined
Sep 3, 2019
Messages
84
Hi there,
I got a query that sums all set values and actual values of every Business ratio. But I can't select the names of my Business ratios. Maybe someone got an idea how to get both ? Everytime I want to select the name I get a warning that my aggregation function won't work with the name selection.

(aa1.jpg is my result and ab1.jpg is my SQL Code. I know that I didn't select the Name. The name is in tbl_kz.name)


Best regards
Georg
 

Attachments

  • aa1.JPG
    aa1.JPG
    13.3 KB · Views: 55
  • ab1.JPG
    ab1.JPG
    16.8 KB · Views: 55

June7

AWF VIP
Local time
Today, 06:59
Joined
Mar 9, 2014
Messages
5,423
How are tables tbl_all and tbl_kz related?

Name is a reserved word and should not use reserved words as names for anything.

Should copy/paste SQL statements in post instead of image.
 

georg7

Registered User.
Local time
Today, 07:59
Joined
Sep 3, 2019
Messages
84
Sorry for the waiting time i've been ill.
c3.jpg is the relation of tbl_all and tbl_kz.
My Goal is that the red circled gets me the Name due tbl_kz and that the blue circled is summed.
It should be like this

tbl_kz tbl_all
KennzahlName Sum(Wert)
Produktivität 9.481,78

I tried it with an Group By but it didn't work. I Always get an exception.

SELECT tbl_Kennzahlen.KennzahlName, tbl_all.Wert
FROM tbl_Kennzahlen INNER JOIN tbl_all ON tbl_Kennzahlen.ID = tbl_all.kz_ID
Group By Kennzahlen.KennzahlName;


Best regards
Georg
 

Attachments

  • c3.JPG
    c3.JPG
    17.8 KB · Views: 42
  • c5.JPG
    c5.JPG
    81.7 KB · Views: 43
  • c4.JPG
    c4.JPG
    19.6 KB · Views: 41

June7

AWF VIP
Local time
Today, 06:59
Joined
Mar 9, 2014
Messages
5,423
And what was the exception message?

Have to include all fields in the SELECT in the GROUP BY if field is not an aggregate calc. Not seeing aggregate calc in that query. Blue circle is on Soil field yet it is not referenced in query.
 

georg7

Registered User.
Local time
Today, 07:59
Joined
Sep 3, 2019
Messages
84
Thank you
I've done it now with
SELECT tbl_Kennzahlen.KennzahlName, tbl_all.kz_ID, Sum(tbl_all.Wert) AS WertALL, Sum(tbl_all.Soll) AS SollALL, tbl_all.Ziel
FROM tbl_Kennzahlen INNER JOIN tbl_all ON tbl_Kennzahlen.ID = tbl_all.kz_ID
WHERE (((tbl_all.grp_ID)=1) AND ((tbl_all.ab_ID)=1))
GROUP BY tbl_Kennzahlen.KennzahlName, tbl_all.kz_ID, tbl_all.Ziel
ORDER BY tbl_all.kz_ID;

You saved me again June7 :)
Best regards
Georg
 

Users who are viewing this thread

Top Bottom