using group by
lazarus / 16-Databases

----------------------------------------------------------------------
qirayzimha · 2007-06-19 01:40

Can I use "Group by " in TMySQLQuery..?
like
Code: [Select]
SELECT sum(totalofafield) FROM atable
WHERE MONTH(adate)=6
GROUP BY adate

Thats OK in Mysql...in lazarus...I dont know...

----------------------------------------------------------------------
felipemdc · 2007-06-19 06:33

Just test and see if it works. Lazarus won't put arbitrary
restrictions about what kinds of queries you can make.

----------------------------------------------------------------------
Smoky555 · 2007-07-03 05:46

no, it's not working in Lazarus

I use mysql lib v 4.1 (Lazarus on Windows, MySQL Server on Linux)
the query is
Code: [Select]
SELECT
        dst_addr, SUM(d_octets) AS field1
FROM
        flows
WHERE(
((src_addr = 12345)) AND
(`timestamp` >= 20070703000000) AND
(`timestamp` <= 20070703235959)
)
group
        by dst_addr

when i open the query, i get an error. i look into mysql log-files on
server, and i see, that query for server looks like this:
Code: [Select]
SELECT
        dst_addr, SUM(d_octets) AS field1
FROM
        flows
WHERE(
(
((src_addr = 123456)) AND
(`timestamp` >= 20070703000000) AND
(`timestamp` <= 20070703235959)
)
group
        by dst_addr)


When i use query without GROUP BY options, it's work fine.

what's wrong with ... with me, with server or with lazarus?

P.S. Sorry for my bad english

----------------------------------------------------------------------
N-drew · 2007-09-26 17:53

Please!
If anyone have a solution to evade this ")" problem, share with us!

----------------------------------------------------------------------
ssamayoa · 2007-09-26 21:03

It seems that db access layer is changing the sql statement.

Which components are using you?

SQLdb, Zeos, other?

I not Zeos, you should try it. Is very mature and too far more feature
richer than SQLdb.

Regards.

----------------------------------------------------------------------
DougNettleton · 2007-09-26 21:26

With a table created as follows:

create table testfile (fund char(4), code char(2), amt1 numeric(9,2))
;

and loaded with:

insert into testfile values('0001', '01', 10.5);
insert into testfile values('0001', '02', 11.6);
insert into testfile values('0001', '03', 12.7);
insert into testfile values('0002', '01', 10.5);
insert into testfile values('0002', '02', 21.0);
insert into testfile values('0002', '03', 31.5);

All of the following work as expected with MySQL 5.0 connection from
Lazarus - recent snapshot

select fund, sum(amt1) as totamt from testfile
where code >= '02'
group by fund

select fund, sum(amt1) as totamt from testfile
where (code = '02') or (code = '03')
group by fund

select fund, sum(amt1) as totamt from testfile
where ((code = '02') or (code = '03'))
group by fund

so I doubt the problem is related to the "group by" clause

Good Luck!
Doug

----------------------------------------------------------------------
N-drew · 2007-09-27 12:50

For the ")" problem Smoky555 gave me the solution.

Before every SQLQuery.Open you have to write SQLQuery.ParseSQL;
This works for me.