MySQL add shared column

Question

I need to query this DB to get each row, but also the SUM of one of the values ​​of the result column. I could use php to get the total value, but then I would need to run two loops to get the total (which is at the top above the results). Therefore, I would prefer the query to catch it and just make a “final” line, but the only way I worked was to use a subquery, which is essentially a repetition of the original query. Is there a better way?

SELECT CONCAT(u.firstname, ' ', u.lastname ) name, u.id, s.description, s.shiftstart, s.shiftend, (SELECT SUM( TIME_TO_SEC( TIMEDIFF( shiftend, shiftstart ) ) ) /3600 FROM shifts WHERE id = '$user' AND DATE( shiftstart ) BETWEEN '$start' AND '$end') total FROM shifts s INNER JOIN users u ON ( s.id = u.id ) WHERE s.id = '$user' AND DATE( shiftstart ) BETWEEN '$start' AND '$end' ORDER BY shiftstart 

The above work and outputs:

 name id description shiftstart shiftend total Joe User joeuser Stuff 2009-01-05 07:45:00 2009-01-05 12:15:00 39.5000 Joe User joeuser Stuff 2009-01-05 13:00:00 2009-01-05 17:00:00 39.5000 Joe User joeuser Stuff 2009-01-06 07:45:00 2009-01-06 10:45:00 39.5000 Joe User joeuser Stuff 2009-01-06 10:45:00 2009-01-06 12:45:00 39.5000 Joe User joeuser Stuff 2009-01-06 13:30:00 2009-01-06 14:30:00 39.5000 Joe User joeuser Stuff 2009-01-06 14:30:00 2009-01-06 17:00:00 39.5000 Joe User joeuser Stuff 2009-01-07 09:45:00 2009-01-07 14:00:00 39.5000 Joe User joeuser Stuff 2009-01-07 15:00:00 2009-01-07 17:00:00 39.5000 Joe User joeuser Stuff 2009-01-08 08:00:00 2009-01-08 12:15:00 39.5000 Joe User joeuser Stuff 2009-01-08 13:15:00 2009-01-08 17:00:00 39.5000 Joe User joeuser Stuff 2009-01-09 07:45:00 2009-01-09 10:45:00 39.5000 Joe User joeuser Stuff 2009-01-09 11:45:00 2009-01-09 15:15:00 39.5000 Joe User joeuser Stuff 2009-01-09 15:15:00 2009-01-09 17:00:00 39.5000 

This is what I need, but probably not the best way to get it.

Answer 1

MySQL supports a special group-by modifier called ROLLUP .

 SELECT CONCAT(u.firstname, ' ', u.lastname ) name, u.id, s.description, s.shiftstart, s.shiftend, SUM( TIME_TO_SEC( TIMEDIFF( shiftend, shiftstart ) ) ) /3600 total FROM shifts s INNER JOIN users u ON ( s.id = u.id ) WHERE s.id = ? AND DATE( shiftstart ) BETWEEN ? AND ? GROUP BY u.id, s.shiftstart WITH ROLLUP ORDER BY shiftstart; 

Answer 2

The best way to do this is with code. People continue to insist on using SQL, which is relational algebra, to perform procedural duties. Attempting a SQL procedural procedure is always a bad idea in terms of complexity and performance. Take this advice from a professional database administrator.

Run two queries from your code. Print a larger set first, and then the full line in whatever format you want. Your queries will be smaller and simpler, your productivity will improve, and you will get the desired result.

Some other tips are that disk space is cheap, and most database tables are read much more often than they are written. Configure the insert / update trigger (if possible, in MySQL) to populate a separate column with calculated fields of the type " CONCAT(u.firstname,' ',u.lastname) " and use it for queries. The functions in the line do not scale and will kill your DBMS performance as it increases.

Answer 3

Everything can be better compared; the question is in comparison with what, right?

The key question you have indicated is that you want the amount to be at the top of the results; Crystal Reports and other applications control this kind of magic, being a 2-pass engine. The first pass gets the results.

There are trade-offs with any solution. If you get a separate “final” line, then the recipient application will need to either cut it from the results or play another trick to hide it.

One possibility, which could be an option if you don’t write to 1 million hit / hr websites, is to simply make 2 calls - one for overhead information such as name, TOTAL Time, etc. then for details ... From the query you select the results of one person.

We are all for saving overhead and bandwidth, but sometimes, it's just better ...

EDIT: Pax beat me up to the save button ... lol ...

Answer 4

Use the mysql extension provided specifically for this purpose, as described in Bill Carvin's answer (which I confirmed myself).

If this was not available, option 2 will be another SQL statement: SELECT SUM ...) SQL is really optimized to be extremely efficient for this kind of thing, compared to any cycle of procedural code that is likely to write.