Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Marcin,
Can you post your QV or at least an image of what you're seeing?
Karl
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;
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
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
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