Aggregate

  
Hello

I'm using an Aggregate for calculate Total of some records using a Function SUM,GROUP BY etc ... but i have a problems with a reppetions with some recordsrecords so how can i avoid this matter please take a look at this attach
best cheers 
I'm sorry, but I can't make anything from either your description or the image. Could you please explain your problem in more detail?
Kilian Hekhuis wrote:
I'm sorry, but I can't make anything from either your description or the image. Could you please explain your problem in more detail?
 Hi Kilian
I have Two tables Piece-Reparation and Activity,the first contains Total_Price1 of Pieces_Rechange ,the second contains the Total_Price2 of workforce,
in my Agregate i have to use the  function Sum(Total_Price1) and  Sum(Total_Price2) 

but the probleme is when i add two  records on the first Table and add just one record on the second Table ,the Function Sum(Total_Price2) give the wrong result for eg:
the records Table1:
Total_Price1 =100
Total_Price1=200
  Sum(Total_Price1)=300

Total_Price2 =1000
  Sum(Total_Price2)=2000 the double result as you see here so what is the source of this double result?

Knowing that those two tables are many to many relationship the first one between 'Reparation' and 'Pieces_Rechange' ,the Second between 'Reparation' and 'Operateur'

i hope you to understand my issus please
best cheers 
 
The problem is with the expression (or lack of) used to do the join between the 2 tables

From what I'm understanding you are doing a cross join with both tables, and you are getting the correct output, even though it's not the output you want...

You will have something like (A and B records from first table, C from the second):
row with A + C details
row with B + C details

This will give the outcome that you explained (correct sum on the first table details, double the sum of the second details)

You need to "fix" the relationship expression for this join, or do another aggregate to do the second sum...

Best regards,
PC
Solution
Hi Fati,

I think I understand your problem, but it has nothing to do with aggregates per se. If you were to hand code the SQL of that query in an Advanced Query, you'd also get double results. SUM() sums the columns of all rows, and since your 2nd table is selected twice, the total is double.

The only solution I can think of is creating an Advanced Query, and do some trickery like explained here or here.
Solution
Pedro Coelho wrote:
The problem is with the expression (or lack of) used to do the join between the 2 tables

From what I'm understanding you are doing a cross join with both tables, and you are getting the correct output, even though it's not the output you want...

You will have something like (A and B records from first table, C from the second):
row with A + C details
row with B + C details

This will give the outcome that you explained (correct sum on the first table details, double the sum of the second details)

You need to "fix" the relationship expression for this join, or do another aggregate to do the second sum...

Best regards,
PC
 Hello Pedro

I cannot make a cross join between with both tables, as i sad the both of them is two many to many relationship the first is "Activity" between Reparation and Operator then the second is "Rep_Piéces" between "Reparation" and "Pieces" 

 
Fati,

Did you check the links I posted?
Hello Kilian
That is the request what i do but no result, so is there another way to accomplish that with an Aggregate because i think we can resolve this error just with fixin th cross join so please help
best cheers

SELECT {Activites}.[Id], {Activites}.[ReparationID] ,sum({Activites}.[MainOeuvreRep])
from {Activites}
join (

        SELECT {TypeReparationPieces}.[Id],sum({TypeReparationPieces}.[PrixTotal])
from {TypeReparationPieces}
join   {Reparation} on {Reparation}.[Id]={TypeReparationPieces}.[ReparationID]
join {Piece_Rechange} on {Piece_Rechange}.[Id]={TypeReparationPieces}.[PiecesRechangeID]
 
) on {Reparation} on {Reparation}.[Id]={Activites}.[ReparationID]
group by

{Activites}.[Id],{Activites}.[ReparationID]

Hi

As you see at this attachement, those is my entities, i also have the same probleme with the double result please help,i don't think there is no result with the OutSystems Plateform !!

best cheers
Sorry i forgot to signalise the records what i want to Sum,then if you give a wa y to avoid the double result,realy i'm so quik because i have to accomplish this task rapidly,

i will be happy if i resolve this issus

best cheers
Hello Fati,

As i understand you only just want to have the sum of the pieces cost , and the work costs in relation of each reparation.
why don't you use mutliple select in stead of a join,


somethink like

select {reparation}.*
,
(
select sum ({activities}.[mainoeuvrerep]
from {activities}
where {actitvities}.[reparationid]=rep.[id]
) total_mainoeuvre
,
(
select sum({typereparationpiece}.[prixtotal])
from {typereparationpiece}
where {typereparationpiece}.[reparationid]=rep.[id]
)
from {repartion}
order by {reparation}.[id]


you put that in a structure of
your reparation table and 2 currency.

That avoid your double records generated by the joins

Bon boulot


didier croughs wrote:
Hello Fati,

As i understand you only just want to have the sum of the pieces cost , and the work costs in relation of each reparation.
why don't you use mutliple select in stead of a join,


somethink like

select {reparation}.*
,
(
select sum ({activities}.[mainoeuvrerep]
from {activities}
where {actitvities}.[reparationid]=rep.[id]
) total_mainoeuvre
,
(
select sum({typereparationpiece}.[prixtotal])
from {typereparationpiece}
where {typereparationpiece}.[reparationid]=rep.[id]
)
from {repartion}
order by {reparation}.[id]


you put that in a structure of
your reparation table and 2 currency.

That avoid your double records generated by the joins

Bon boulot

 
 Hi
i have an error enexpected 'from' in sql statement SQL1 as you'll see at this attach
 
didier croughs wrote:
Hello Fati,

As i understand you only just want to have the sum of the pieces cost , and the work costs in relation of each reparation.
why don't you use mutliple select in stead of a join,


somethink like

select {reparation}.*
,
(
select sum ({activities}.[mainoeuvrerep]
from {activities}
where {actitvities}.[reparationid]=rep.[id]
) total_mainoeuvre
,
(
select sum({typereparationpiece}.[prixtotal])
from {typereparationpiece}
where {typereparationpiece}.[reparationid]=rep.[id]
)
from {repartion}
order by {reparation}.[id]


you put that in a structure of
your reparation table and 2 currency.

That avoid your double records generated by the joins

Bon boulot

 
 Hello didier croughs
thank you for your quik reply,that exactly what i want but just i want to display another attribute from another entities as you'll see at this attachement please i'm trying to add it but i have an error query,,,, so i will to attach you what i want exactly to display from my query
best cheers
 
Fati Zahra wrote:
didier croughs wrote:
Hello Fati,

As i understand you only just want to have the sum of the pieces cost , and the work costs in relation of each reparation.
why don't you use mutliple select in stead of a join,


somethink like

select {reparation}.*
,
(
select sum ({activities}.[mainoeuvrerep]
from {activities}
where {actitvities}.[reparationid]=rep.[id]
) total_mainoeuvre
,
(
select sum({typereparationpiece}.[prixtotal])
from {typereparationpiece}
where {typereparationpiece}.[reparationid]=rep.[id]
)
from {repartion}
order by {reparation}.[id]


you put that in a structure of
your reparation table and 2 currency.

That avoid your double records generated by the joins

Bon boulot

 
 Hello didier croughs
thank you for your quik reply,that exactly what i want but just i want to display another attribute from another entities as you'll see at this attachement please i'm trying to add it but i have an error query,,,, so i will to attach you what i want exactly to display from my query
best cheers
 
 
Hello

it's done,thanks
 i have just one thing missing,is how can i display (num_ordreEngin,Marque) from entity 'Engin' as i have in 'BaseDonné' Picture
select {Reparation}.[Id]
,
(select {Type_Reparation}.[Nom]
from {Type_Reparation}
where {Type_Reparation}.[Id]={Reparation}.[TypeReparationId]
),
(
select {Reclamation}.[Id]
from {Reclamation},
where {Reclamation}.[Id]={Reparation}.[ReclamationID]
),
(
select sum ({Activites}.[MainOeuvreRep])
from {Activites}
where {Activites}.[ReparationID]={Reparation}.[Id]
) total_mainoeuvre
,
(
select sum({TypeReparationPieces}.[PrixTotal])
from {TypeReparationPieces}
where {TypeReparationPieces}.[ReparationID]={Reparation}.[Id]
)
from {Reparation}
order by {Reparation}.[Id]

Thank you so much
Try that , you can mix the join and the sub querries .I 've removed you first sub queery and replaced by a join.
For the rest i 've added 2 left join but it might be simply 2 join , you have to check.

select {Reparation}.[Id],{Type_Reparation}.[Nom],{engin}.[numordre],{engin}.[marque]
, ( select sum ({Activites}.[MainOeuvreRep]) from {Activites}
     where {Activites}.[ReparationID]={Reparation}.[Id]
) total_mainoeuvre
, ( select sum({TypeReparationPieces}.[PrixTotal]) from {TypeReparationPieces}
where {TypeReparationPieces}.[ReparationID]={Reparation}.[Id] )

from {Reparation}
join {Type_Reparation} on {Type_Reparation}.id={Reparation}.[typereparationId]
left join {Reclamation} on {Reclamation}.id={Reparation}.[ReclamationID]
left join {engin} on {engin}.id={reclamation}.{enginid}
 order by {Reparation}.[Id]


Regards
Hi didier

good job,thanks 

just one thing is when can i put exactly this agregate  :
Sum({TypeReparationPieces}.[PrixTotal])+{Activites}.[MainOeuvreRep]) because i need also the Total of those two totals

thank you for your reply
I Add this but i have wrong result 
select sum({TypeReparationPieces}.[PrixTotal]+{Activites}.[MainOeuvreRep])
from {TypeReparationPieces},{Activites}
where {TypeReparationPieces}.[ReparationID]={Reparation}.[Id] and
{Activites}.[ReparationID]={Reparation}.[Id]
That's clear with such a querry , you never have a good total.

Compute the 2 sum separatly as it was.

after you can add the values when showing it in your form.

but if you want to have the total walue in the query

you can
add the 2 values in your querry
select sum (...)
+
select sum (...)

or encapsulate the querry in a another querry

select AA. * from
(
select ....(the querry as before ! each fields must have a name)
)AA
 this option give you the possibility to make a where on one of your sum , or order on your sum , that's an good option if you plan to put it in a screen with filters , and orders by.



best regards


didier croughs wrote:
That's clear with such a querry , you never have a good total.

Compute the 2 sum separatly as it was.

after you can add the values when showing it in your form.

but if you want to have the total walue in the query

you can
add the 2 values in your querry
select sum (...)
+
select sum (...)

or encapsulate the querry in a another querry

select AA. * from
(
select ....(the querry as before ! each fields must have a name)
)AA
 this option give you the possibility to make a where on one of your sum , or order on your sum , that's an good option if you plan to put it in a screen with filters , and orders by.



best regards

 
 good job
thanks a lot
 
Hi didier croughs
I have to add these condtions on my querry because i need to make serch from two inputs DateFrom and DateTo as you'll see at this attachement .knowing that i create two parameters input data type Date and they have
 
Session.FacturationDateFrom   
Session.FacturationDateTo



select {Reparation}.[Id],{Reparation}.[Date_Debut],{Reclamation}.[Id],{Type_Reparation}.[Nom],{Engins}.[Num_Ordre],{Engins}.[Marque]
,{Unite}.[Nom], ( 
select sum ({Activites}.[MainOeuvreRep]) 
from {Activites}
     where {Activites}.[ReparationID]={Reparation}.[Id]
) total_mainoeuvre
,
 ( select sum({TypeReparationPieces}.[PrixTotal])
from {TypeReparationPieces}
where {TypeReparationPieces}.[ReparationID]={Reparation}.[Id] 
)

from {Reparation}

join {Type_Reparation} on {Type_Reparation}.[Id]={Reparation}.[TypeReparationId]
left join {Reclamation} on {Reclamation}.[Id]={Reparation}.[ReclamationID]
left join {Engins} on {Engins}.[Id]={Reclamation}.[EnginID]
left join {Unite} on {Reclamation}.[UniteID]={Unite}.[Id]

where 
@DateFrom  = 'NullDate()' or {Reparation}.[Date_Debut] >= @DateFrom and
@DateTo  = 'NullDate()' or {Reparation}.[Date_Debut] <= @DateTo
 
 order by {Reparation}.[Id]


 
Session.FacturationDateFrom = NullDate() or Activites.DateActivite >= Session.FacturationDateFrom
Session.FacturationDateTo = NullDate() or Activites.DateActivite <= Session.FacturationDateTo
Unite.UserID = Session.UserId
Fati Zahra wrote:
Hi didier croughs
I have to add these condtions on my querry because i need to make serch from two inputs DateFrom and DateTo as you'll see at this attachement .knowing that i create two parameters input data type Date and they have
 
Session.FacturationDateFrom   
Session.FacturationDateTo



select {Reparation}.[Id],{Reparation}.[Date_Debut],{Reclamation}.[Id],{Type_Reparation}.[Nom],{Engins}.[Num_Ordre],{Engins}.[Marque]
,{Unite}.[Nom], ( 
select sum ({Activites}.[MainOeuvreRep]) 
from {Activites}
     where {Activites}.[ReparationID]={Reparation}.[Id]
) total_mainoeuvre
,
 ( select sum({TypeReparationPieces}.[PrixTotal])
from {TypeReparationPieces}
where {TypeReparationPieces}.[ReparationID]={Reparation}.[Id] 
)

from {Reparation}

join {Type_Reparation} on {Type_Reparation}.[Id]={Reparation}.[TypeReparationId]
left join {Reclamation} on {Reclamation}.[Id]={Reparation}.[ReclamationID]
left join {Engins} on {Engins}.[Id]={Reclamation}.[EnginID]
left join {Unite} on {Reclamation}.[UniteID]={Unite}.[Id]

where 
@DateFrom  = 'NullDate()' or {Reparation}.[Date_Debut] >= @DateFrom and
@DateTo  = 'NullDate()' or {Reparation}.[Date_Debut] <= @DateTo
 
 order by {Reparation}.[Id]


 
Session.FacturationDateFrom = NullDate() or Activites.DateActivite >= Session.FacturationDateFrom
Session.FacturationDateTo = NullDate() or Activites.DateActivite <= Session.FacturationDateTo
Unite.UserID = Session.UserId