Totals for columns
lazarus / 16-Databases

----------------------------------------------------------------------
Robster · 2011-02-28 14:27

I would like to produce a grid with  columns   Total Year,  Jan,
Feb.....etc
I would also like a total for each column at the bottom.

Is SQL Union going to provide me with what I want at the bottom of the
grid?

----------------------------------------------------------------------
sorinr · 2011-02-28 15:18

Hi Robster,

In order to get an answer please give us much info as you can on
exactly what you want to do. What database are you using, what Lazarus
controls are you plan to use, what type of fields do you want to
include in a sum function, etc etc.

The idea is that if you provide as much info as possible from the
beggining the faster you will probably get an answer.

----------------------------------------------------------------------
marc_dsb · 2011-03-01 02:06

Hi
To add columns to a TDBGrid insert a new column in "Table1" call eg.
"Total"
then insert a TDBEdit on the form, modifying the following properties:
Datafield: Total (insert the last column)
Use the following code associated with a TButton.

Procedure TForm1.Button4Click(Sender: TObject);
begin
       sqlquery2.active:=false;
       sqlquery2.SQL.Clear;
       sqlquery2.SQL.Text:='select sum(salidas) as total from
tabla1;';
       sqlquery2.ExecSQL;
       sqlquery2.Active:=true;
end;             

Not if what you want, is inelegant but it works.
Was tested with MySQL 5 and WinXP

----------------------------------------------------------------------
DougNettleton · 2011-03-01 16:59

This worked for me ...

select fundnumb, emprnumb, sum(tothrs) as tothrs,
  sum(tothrs2) as tothrs2, sum(calcamt) as calcamt from batch
where fundnumb = '0005'
group by 1, 2
union select fundnumb, 'Total' as emprnumb, sum(tothrs) as tothrs,
  sum(tothrs2) as tothrs2, sum(calcamt) as calcamt from batch
where fundnumb = '0005'
group by 1


Note: emprnumb is a char(5) field

So, I guess the answer is "Yes"

HTH,

Doug

----------------------------------------------------------------------
krexon · 2011-03-02 13:16

Of course, it works (union). I use it for few years, but these values
are in rows like other data. The best solution is to put aggregated
data (sums and so on) in footer which is always visible, not only when
you scroll grid to bottom. I didn't find any grid with static footers
(always visible at bottom of grid). The second problem is sorting
columns. Aggregated data are sorted as other data

----------------------------------------------------------------------
JD · 2011-03-05 09:30

Quote from: krexon on March 02, 2011, 02:16:10 pmI didn't find any
grid with static footers (always visible at bottom of grid). The
second problem is sorting columns. Aggregated data are sorted as other
data

RxDBGrid & AJDBGrid both have static footers that are always visible
at the bottom of the grid. They might be what you are looking for.

----------------------------------------------------------------------
krzynio · 2014-01-26 20:38

What is AJDBGrid? Can you provide any link, please?