[SQLite] Problema con SUM e JOIN

[fermat]

ciao!

ho un problema con le SUM e le JOIN in un db SQLIte.
in pratica questo sono i dati che estraggo:

22    5038    ADR1    SALA PRELIEVI    SALA PRELIEVI    50    2
22    5038    ADR1    SALA PRELIEVI    SALA PRELIEVI    50    1
23    5039    ADR2    SALA ASPETTO    SALA ASPETTO    100    4
23    5039    ADR2    SALA ASPETTO    SALA ASPETTO    100    3
24    5040    ADR3    BAGNO UOMINI    BAGNO SALA ATTESA UOMINI    30    5
26    5041    ADR4    BAGNO DONNE    BAGNO SALA ATTESA DONNE    30    7
27    5042    ADR5    TERRAZZO    TERRAZZO    300    8
28    5043    ADR6    BALCONE    BALCONE    25    9
25    5044    ADR7    BAGNO DISABILI    BAGNO DISABILI    30    6

come vedete le stanze sono ripetute per via della JOIN.
io però avrei necessità di fare la SUM della penultima colonna.
se faccio così, ovviamente mi sballa il calcolo:

SELECT SUM(area_in_square_meters) AS summq
 FROM Rooms
 INNER JOIN TodoList ON TodoList.rooms_id = Rooms.rooms_id
 WHERE
 Rooms.Mugs_id = 229
 AND TodoList.mugs_id = 229
 AND DATE(TodoList.data_from) = "2020-12-09"
 AND "08:50" BETWEEN SUBSTR(TodoList.data_from, 12, 5) AND SUBSTR(TodoList.data_to, 12, 5)
 AND TodoList.work_plan_type_id = 1

in questi casi come si può fare??

[optime]

normalmente la SUM() - come tutte le funzioni di aggregazione - vuole una GROUP BY(), che però non vedo nella tua query

[optime]

e comunque il quesito non è che sia chiarissimo... che vuol dire "come vedete le stanze sono ripetute per via della JOIN." ?

[fermat]

ciao!

il problema è che non saprei per cosa raggruppare.
nel senso che questa query tira fuori il record set precedente:

SELECT 
 Rooms.id AS id, 
 Rooms.rooms_id AS roomsId,
 Rooms.code AS code, Rooms.name AS name,
 Rooms.description AS description, 
 Rooms.area_in_square_meters AS areaInSquareMeters,
 Rooms.mug_layout_id AS mugLayoutId,
 Rooms.mugs_id AS mugsId, 
 TodoList.start_date AS startDate,
 TodoList.end_date AS endDate,
 TodoList.id
 FROM Rooms
 INNER JOIN TodoList ON TodoList.rooms_id = Rooms.rooms_id
 WHERE
 Rooms.Mugs_id = 229
 AND TodoList.mugs_id = 229
 AND DATE(TodoList.data_from) = "2020-12-09"
 AND "08:50" BETWEEN SUBSTR(TodoList.data_from, 12, 5) AND SUBSTR(TodoList.data_to, 12, 5)
 AND TodoList.work_plan_type_id = 1

se io facessi nella SUM una GROUP BY per code ad esempio, mi verrebbero fuori diversi records con la SUM.
cioè una SUM per stanza alla fine.
mentre io ho bisogno della somma del campo area_in_square_meters di tutte le stanze.
solo che con la JOIN alcuni records si duplicano, e la SUM prende più volte alcune stanze.

non so se mi sono riuscito a spiegare.

[optime]

non capisco. Se una stanza è di 50mq, se la sommi per due eventi diventa di 100mq... è corretto? Poi, fa' un esempio di come vorresti l'ouput

[fermat]

allora, il concetto è questo.

ad ogni stanza è associata una o più prestazioni da eseguire.
ecco perchè con la join, nel caso di più prestazioni, mi tira fuori più volte la stessa stanza.

ma nel computo totale la stanza deve essere considerata una sola volta.
quindi supponiamo che io debba prendere le stanze partendo dalle prestazioni da fare in un determinato giorno e orario:

STANZA 1 - PRESTAZIONE 1 - 50 mq
STANZA 1 - PRESTAZIONE 2 - 50 mq
STANZA 2 - PRESTAZIONE 1 - 100 mq

nel computo totale mi deve uscire 150 mq, non 200 mq.
perchè a me servirebbe il totale dei mq delle stanze, a prescindere da quante prestazioni abbiamo al loro interno.

[optime]

da quello che ho capito, tu vuoi SIA la lista delle singole prestazioni (e quindi le stanze vengono correttamente ripetute) SIA una riga di totale, ma fatta come?

[fermat]

in verità sono due query distinte.

da una parte c'è la lista, che è raggruppata per stanza in quanto non si devono vedere i "doppioni" (che è usata in una singola schermata).

dall'altra ci sta il totale MQ (che visualizzo in diverse schermate).
che, però, mi da risultati errati in quanto non faccio raggruppamenti.
non facendoli mi sono accorto che mi somma anche le righe "doppie".
mi servirebbe una query "secca" che mi da solo il totale MQ, senza considerare le righe "doppi" derivanti dalla JOIN.

spero di essermi riuscito a spiegare!!

nel frattempo ho risolto usando la query della lista, e sommando gli MQ scorrendo il record set.
non è una soluzione pulita, ma al momento è l'unico modo che ho trovato.

[optime]

SELECT SUM(areaInSquareMeters) FROM (
SELECT DISTINCT rooms.Id, areaInSquareMeters FROM (
(
SELECT
Rooms.id AS id,
Rooms.rooms_id AS roomsId,
Rooms.code AS code, Rooms.name AS name,
Rooms.description AS description,
Rooms.area_in_square_meters AS areaInSquareMeters,
Rooms.mug_layout_id AS mugLayoutId,
Rooms.mugs_id AS mugsId,
TodoList.start_date AS startDate,
TodoList.end_date AS endDate,
TodoList.id
FROM Rooms
INNER JOIN TodoList ON TodoList.rooms_id = Rooms.rooms_id
WHERE
Rooms.Mugs_id = 229
AND TodoList.mugs_id = 229
AND DATE(TodoList.data_from) = "2020-12-09"
AND "08:50" BETWEEN SUBSTR(TodoList.data_from, 12, 5) AND SUBSTR(TodoList.data_to, 12, 5)
AND TodoList.work_plan_type_id = 1
) X
) Y

[optime]

c'è una parentesi di troppo (quella isolata della terza riga), non riesco a modificare il messaggio