Summary field false total

[#1]

Hello Everyone,

I'm new to QV and have some questions, becouse I found a problem within my report.

I'm trying to calculate SUM on field Punktacja_Punkty from Table Punktacja. This table has the same ID field IDNagroda as the field in Table Kotly in which I gather the data (some serial numbers, users name and productID).

Table Kotly	Table Punktacja	Table Users
User_ID	IDNagrody	User_ID
IDNagrody	Punktacja_Nagr	Users_UserName
Kotly_ID	Punktacja_Ean	Users_Passw
Kotly_Kod	Punktacja_Punkty
Kotly_DataZa
Kotly_Rok
Kotly_Dystr

The data from red fields and green are relational. Everything works during the data lookup ok but I wonder why SUM(Punktacja_Punkty) expression is not creating total of the filtered content. I.E. the sum for data below is only 26 instead of 226. Infact count(Kotly_ID) = 42 as it should.

Kotly_ID	Kotly_Kod	Punktacja_Punkty	Users_UserName
3556	1	8	username
3555	2	8	username
3557	3	8	username
3552	4	8	username
3553	5	8	username
3554	6	8	username
3559	7	8	username
3558	8	8	username
3550	9	3	username
3547	10	3	username
3549	11	3	username
3548	12	3	username
3551	13	3	username
3452	14	3	username
3448	15	3	username
3447	16	3	username
3457	17	3	username
3456	18	3	username
3453	19	3	username
3451	20	3	username
3449	21	3	username
3455	22	3	username
3454	23	3	username
3450	24	3	username
3458	25	3	username
3437	26	3	username
3436	27	3	username
3439	28	3	username
3441	29	3	username
3443	30	3	username
3445	31	3	username
3442	32	3	username
3440	33	3	username
3435	34	3	username
3438	35	3	username
3444	36	3	username
3446	37	3	username
3564	38	15	username
3562	39	15	username
3560	40	15	username
3561	41	15	username
3563	42	15	username

Can you gice me a feedback how to summarize corectly and how to add the sum field i.e in TableBox Object or MultiBox Obj?

Best regards and sorry if it's to simple...

Marcin

[pover]

Marcin,

Can you post your QV or at least an image of what you're seeing?

Karl

[#3]

Hi Karl,

thanks gor replay. As attachements you will find 3 files - textBox with properities, same as TableBox and tables schema. Below I paste the script tables.

Best regards and thx for answering.

SQL SELECT Id as Kotly_ID,

    kod as Kotly_Kod,

    typkotla as IDNagrody,

    dataZakupu as Kotly_DataZakupu,

    rok as Kotly_Rok,

    DataWpisu as Kotly_DataWpisu,

    dystrybutor as Kotly_Dystrybutor,

    userId as User_ID

FROM db168409.Kotly;

SQL SELECT ID as IDNagroda,

    Nazwa as IDNagrody,

    Opis as Nagrody_Opis,

    Foto as Nagrody_Foto,

    Punkty as Nagrody_Punkty

FROM db168409.Nagrody;

SQL SELECT ID as NagrodyZreal_ID,

    IDNagrody as IDNagrody,

    Rozmiar as NagrodyZreal_Rozmiar,

    Punkty as NagrodyZreal_Punkty,

    IDUzytkownik as NagrodyZreal_IDUzytk,

    Data as NagrodyZreal_Data

FROM db168409.NagrodyZreal;

SQL SELECT ID as IDNagrody,

    NazwaKotla as Punktacja_NazwaKotla,

    KodEanKotla as Punktacja_EanKotla,

    Punkty as Punktacja_Punkty

FROM db168409.Punktacja;

SQL SELECT ID as User_ID,

    usern as Users_UserName,

    passwd as Users_Passwd,

    role as Users_Role

FROM db168409.Users;

SQL SELECT uemail as User_ID,

    imie as UsersFull_Imie,

    nazwisko as UsersFull_Nazwisko,

    sapno as UsersFull_SapNo,

    company as UsersFull_Company,

    kodpoczt as UsersFull_KodPoczt,

    miasto as UsersFull_Miasto,

    adres as UsersFull_Adres,

    telefon as UsersFull_Telefon

FROM db168409.UsersFull;

[pover]

Hi Marcin,

The problem is that the table Punktacja is a reference table like a price list or grade table and doing a sum just on that table will sum up the amounts without respecting how many times it is referred to in the the table Kotly.  You should either do a join between the Kotly and Punktacja table in the script or add a quantity column in the Kotly table to get the sum you are looking for.

Out of curiosity Kotly is drum and Punktacja is a grade scale?  I'm just wondering how good the polish-english translators are.

Karl

[#5]

Hi Karl,

yes the Punktacja is a grade scale, but Kotly in this context are boilers (gas boilers I was thinking of).

afterall I wanted to summarize by the points of Kotly for each of users and also get the participation (count) the boilers by type.

Infact I think I'll try to create one more dataset for this purpose. Do you think its better to join the tables or concatenate them??

BR

Marcin

[pover]

Marcin,  Do a left join between Kotly and Punktacja as you will be adding a column to existing data and not adding rows which would be the case in which you would do a concatenate.

Karl