Summary row at the end of select

[CRO_Tomislav 0]

Dear all.

 

I have two query in relation parent (Query1) - child (Query2)

First query get data about of employees from table employees:

 

id emply_name location

_________________________

1 Martin            London

2 Matija             Paris

3 Ana                Ljubljana

4 Nikola            Stuttgart

 

 

Second query get data about weekly payments for each employee from table payment:

 

id fk_id_emplyee date            payment

_____________________________________________

1 1                       2023-01-07 20,00

2 1                       2023-01-14 15,00

3 1                       2023-01-23 22,00

4 2                       2023-01-07 18,00

5 3                       2023-01-23 25,00

 

I am trying to in Query2 get summary record of payments for currently active record (employee) in DBGrid1 (Query1).

 

Query1 SQL code: SELECT * FROM employees

 

Query2 SQL code:

SELECT id, fk_id_employee, date, payment FROM payment

GROUP BY id

UNION

SELECT 'TOTAL', '','', sum(payment) as total_payment

FROM payment

ORDER BY date

 

Problem is that which ever employee I select in DBGrid1, I always get total for all records in a tables payment.

I need total only for currently active record (employee).

 

THX in advance

 

[mjustin 23]

The SQL for Query2 must contain a WHERE clause to filter out only the rows for the selected employee ID.

 

Also the GROUP BY can not not work because its SELECT clause does not use aggregate functions. GROUP BY id also makes no sense at all, as ID is the record id in the payment table.

 

[CRO_Tomislav 0]

Hello.

 

If I do not use GROUP BY id, I get listed all records from table payments.

 

If I change Query2 in:

 

SELECT id, fk_id_employee, date, payment FROM payment

GROUP BY id

UNION

SELECT 'TOTAL', '','', sum(payment) as total_payment

FROM payment

WHERE fk_id_employee =mDBGrid1.DataSource.DataSet.FieldByName('id').AsString

ORDER BY date

 

I receive data from table payment for each active record in DBGrid1 (employee) related on parent-child but without totals. There is row which should be include TOTAL but a sum fileds are empty...

 

 

[Stano 143]

First, you should study the basics of SQL syntax. In the second query, after UNION, you must have GROUP BY. Doesn't DB slap you?

[programmerdelphi2k 237]

hi @CRO_Tomislav

in fact, exists many ways to do it... you can use this simple SQL statement.

select id_employee, employees.name, sum(value) from salary
join employees on (salary.id_employee = employees.id)
group by id_employee
order by id_employee   // or order by emplyees.name

if you needs just show on screen you can use "FDLocalSQL" with "SQLite" engine to compute your final sql, like this:

Edited January 29, 2023 by programmerdelphi2k

[CRO_Tomislav 0]

Hi.

 

Stano, I am try to learn...

Please, can You please modify a Query2?

 

THX

[CRO_Tomislav 0]

Programerdelphi2k:

Thanks on replay but this is not what I am looking for:

The result of Query2 for employee wizh id=1 shuld look like:

 

id          fk_id_emplyee   date            payment

_____________________________________________

1           1                       2023-01-07 20,00

2           1                       2023-01-14 15,00

TOTAL                                                35,00

 

Query1 and Query2 are parent-child related

 

The SQL code works OK on a single table, if I try to use it on a child table - it does not.

 

THX

Edited January 29, 2023 by CRO_Tomislav
Addition

[programmerdelphi2k 237]

ok, understand you needs a master-detail!

Edited January 29, 2023 by programmerdelphi2k

[Stano 143]

Unfortunately, I don't understand what you want from the description. Especially how the tables are connected to each other!
The above should be done with one SQL that will contain a subquery.
By the way. If some DataSets are connected using UNION, they must have the same data structure. You don't live up to it!
I'm curious what you're running the mentioned SQL in. My DB manager would reject it immediately. Also with a message of some error.

Also tell us what DB you use!!!

[programmerdelphi2k 237]

with Roots as(
select S.id, S.id_employee, S.Value, S.id_employee MyID_Root from Salary S
join Employees on (Employees.id = S.id_employee)
),
Datas as (
select A.id, A.id_employee, A.Value from Salary A
join Employees on (Employees.id = A.id_employee)
)
select B.id_employee, B.value, sum(B.value)+0.00 Total from salary B
join Roots on (Roots.id = B.id_employee)
join Datas on (Datas.id = B.id_employee)
group by B.id_employee

Edited January 29, 2023 by programmerdelphi2k

[programmerdelphi2k 237]

@Stano

in fact, it's not necessary have same structure, but the same type, if one table dont have any field (for example), you can use a "hack" for it!

 

Quote

select id MyIDs, name MyNames, cast('2023-01-01' as date)  MyDates, 0 MyIDEmpl, 0.00 MyVals from employees
union 
select id MyIDs, '' MyNames, paymentday MyDates, id_employee MyIDEmpl, value MyVal from salary

 

[Attila Kovacs 629]

1 hour ago, programmerdelphi2k said:

with Roots as( select S.id, S.id_employee, S.Value, S.id_employee MyID_Root from Salary S join Employees on (Employees.id = S.id_employee) ), Datas as ( select A.id, A.id_employee, A.Value from Salary A join Employees on (Employees.id = A.id_employee) ) select B.id_employee, B.value, sum(B.value)+0.00 Total from salary B join Roots on (Roots.id = B.id_employee) join Datas on (Datas.id = B.id_employee) group by B.id_employee

next issue:  there are 1.5 million records in the salary table and the form takes 10 minutes to open

btw. what the heck is sum(x)+0.00? i that a kind of ISNULL(sum(x),0) ? Also, x shoukd be ifnull checked too.

 

 

[programmerdelphi2k 237]

Anyone who has 1.5mi employees to pay would certainly not be here asking any questions!

 

I preferred to use the old Master-Details, and that's it! would solve!
About the "+0.00" (NOT is not for ISNULL checks) is because FireDac likes to determine data types, so I didn't want to do any checks in the component, to show 8 values on the screen... if you want, do it!

Edited January 29, 2023 by programmerdelphi2k

[Attila Kovacs 629]

23 minutes ago, programmerdelphi2k said:

Anyone who has 1.5mi employees to pay would certainly not be here asking any questions!

 

records.... nevermind. spread your spagetticode in comicbooks

 

 

 

 

Edited January 29, 2023 by Attila Kovacs

[Stano 143]

11 hours ago, programmerdelphi2k said:

n fact, it's not necessary have same structure, but the same type, if one table dont have any field (for example), you can use a "hack" for it!

That's exactly what I meant. I use it regularly. I did not see it in the author of the question. Formatting it makes it hard to read for me.

[weirdo12 19]

If you are using TFDQuery, have you tried using the MasterSource property to connect Query2 to Query1?

 

Query2.IndexFieldNames = fk_id_employee

Query2.MasterFields = id

Query2.MasterSource = Query1

 

You can use TFDLocalSQL and a TFDMemTable to do a summary of Query2.

 

[CRO_Tomislav 0]

9 hours ago, weirdo12 said:

If you are using TFDQuery, have you tried using the MasterSource property to connect Query2 to Query1?

 

Query2.IndexFieldNames = fk_id_employee

Query2.MasterFields = id

Query2.MasterSource = Query1

 

You can use TFDLocalSQL and a TFDMemTable to do a summary of Query2.

 

Hi.

 

Yes, I am using MasterSource porperty to connect Query2 to Query1.

I inherited some Delphi projects and in those project’s I found a many Delphi forms where solutions of summarize columns is solved on that way which You describe...

 

The SQL in Query2 I was recently found on a net and I get idea to reduce some things on a forms in project.

As a Query2 didn't work in MasterSource property connected Query's - I was wonder what is the reason...

 

 

[programmerdelphi2k 237]

@CRO_Tomislav

as I said before, the Master-Details would be your choice for this task, no needs more than this!

• example using "EMPLOYEE.GDB" in Interbase: 
  • Query1 (Customer - your item Names) :               SELECT * FROM CUSTOMER  // where CUSTNO = :CUSTNO 
  • Query2 (Orders - where is the items to sum):  SELECT * FROM ORDERS where CUSTNO=:CUSTNO
    • MasterSource = dsCustomer - MasterField = CUSTNO
  • Query3 (Orders - the sum):                              SELECT sum(AMOUNTPAID) as TOTAL from ORDERS  where CUSTNO = :CUSTNO
    • MasterSource = dsOrders - MasterField = CUSTNO
• "  :CUSTNO   ":  is a param (any name) used to inform the value that you need
• in "Query1", you can inform the param (if you want 1 item) or not (if you want all item)
• in "Query2 and Query3" you need use it

 

Edited January 31, 2023 by programmerdelphi2k

[weirdo12 19]

3 hours ago, programmerdelphi2k said:

@CRO_Tomislav

• in "Query2 and Query3" you need use it

 

IndexFieldNames in Query2 and Query3 should be set to CUSTNO. A parameter is not required.

 

Query3.SQL = ' SELECT CUSTNO, sum(AMOUNTPAID) as TOTAL from ORDERS  GROUP BY CUSTNO';

Edited January 31, 2023 by weirdo12
Adding detail.

[programmerdelphi2k 237]

@weirdo12   that's weird not?

I think that you can be wrong about needs or not "IndexFieldNames" usage!

• WHERE IS the "IndexFieldname" ? really I dont need it!!!  CUSTNO is PrimaryKey and ForeignKey on tables!
  • NOTE: when you set a "index", your masterfield will be defined too!
• GROUP BY is not necessary (and dont have usage here because just 1 record will be the recordset) in Query3 (Detail) because "SUM()"+ param used in Query2 (Master)
• NOTE2: the other values "1221" was defined by FireDAC (It likes this)  ... I just only put it in my Query1 on SQL!

  object EmployeeConnection: TFDConnection
    Params.Strings = (
      'ConnectionDef=EMPLOYEE')
    Connected = True
    LoginPrompt = False
  end
  object CustomerTable: TFDQuery
    Active = True
    Connection = EmployeeConnection
    SQL.Strings = (
      'SELECT * FROM CUSTOMER where custno=1221')  /// just 1221 for tests or dont use this "where" clause for all!!!
  end
  object OrdersTable: TFDQuery
    Active = True
    MasterSource = DataSource1
    MasterFields = 'CUSTNO'
    Connection = EmployeeConnection
    FetchOptions.AssignedValues = [evCache]
    FetchOptions.Cache = [fiBlobs, fiMeta]
    SQL.Strings = (
      'SELECT * FROM ORDERS'
      'where custno = :custno')
    ParamData = <
      item
        Name = 'CUSTNO'
        DataType = ftFloat
        ParamType = ptInput
        Value = 1221.000000000000000000   // defined by FireDAC 
      end>
  end
  object DataSource1: TDataSource
    DataSet = CustomerTable
  end
  object DataSource2: TDataSource
    DataSet = OrdersTable
  end
  object DataSource3: TDataSource
    DataSet = FDQuery1
  end
  object FDQuery1: TFDQuery
    Active = True
    MasterSource = DataSource2
    MasterFields = 'CUSTNO'
    DetailFields = 'CUSTNO'
    Connection = EmployeeConnection
    FetchOptions.AssignedValues = [evCache]
    FetchOptions.Cache = [fiBlobs, fiMeta]
    SQL.Strings = (
      'SELECT sum(amountpaid) FROM ORDERS'
      'where custno = :custno')
    ParamData = <
      item
        Name = 'CUSTNO'
        DataType = ftFloat
        Precision = 16
        ParamType = ptInput
        Value = 1221.000000000000000000  // defined by FireDAC 
      end>
  end

        

 

Edited February 1, 2023 by programmerdelphi2k

[weirdo12 19]

1 hour ago, programmerdelphi2k said:

@weirdo12   that's weird not?

I think that you can be wrong about needs or not "IndexFieldNames" usage!

 

I should have said that an alternative way is to use IndexFieldNames instead of queries with parameters. Both methods produce the same results.

[programmerdelphi2k 237]

what your choice? one for you, one for me!