Delphi, FireDAC, MySQL Query

Question

qClient.SQL.Clear;
qClient.SQL.Text := 'Select C.Lastname,C.Firstname,C.Clino,C.Address,' +
                    'C.City,C.State,C.Zip,C.Phone,C.HerCell,C.HisCell,' +
                    'C.Texting,C.Since,C.Selected,' +
                    'SUM( I.Charges - I.Discount ) as Revenue ' +
                    'from Client C join Invoice I ' +
                    'on I.Clino = C.Clino ' +
                    'where I.Date between :S and :E ' +
                    'and I.Inv = true ' +
                    'group by C.Clino ' +
                    'order by Revenue desc ';
qClient.ParamByName('S').AsDate := dStart;
qClient.ParamByName('E').AsDate := dEnd;
qClient.DetailFields := 'Clino';
qClient.Open();

How can I modify the above code so that the query includes only 'Revenue' values over a minimum (like maybe 1000)? It currently includes all clients.

Thanks, Spyke

forpas · Accepted Answer · 2020-11-20 21:08:17Z

2

You need a HAVING clause:

qClient.SQL.Text := 'Select C.Lastname,C.Firstname,C.Clino,C.Address,' +
                    'C.City,C.State,C.Zip,C.Phone,C.HerCell,C.HisCell,' +
                    'C.Texting,C.Since,C.Selected,' +
                    'SUM( I.Charges - I.Discount ) as Revenue ' +
                    'from Client C join Invoice I ' +
                    'on I.Clino = C.Clino ' +
                    'where I.Date between :S and :E ' +
                    'and I.Inv = true ' +
                    'group by C.Clino ' +
                    'having Revenue > 1000 ' +
                    'order by Revenue desc ';

The HAVING clause applies conditions and filters the results of the query after the aggregation.
In this case Revenue is an aggregated column and could not be used in a WHERE clause which also filters a query, because the conditions of the WHERE clause are applied before the aggregation.

edited Nov 20, 2020 at 21:08

answered Nov 19, 2020 at 21:05

forpas

165k10 gold badges51 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jim Sawyer Over a year ago

The "having" clause solved my problem. How do I acknowledge this? This is my first time to use this forum.

forpas Over a year ago

@JimSawyer You can read this: meta.stackoverflow.com/questions/251078/…

Collectives™ on Stack Overflow

Delphi, FireDAC, MySQL Query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related