0

I am trying to get some data out of my database and stuck at one of the SELECT operations. Basically I have 2 tables, users and invoices: - users contains 4 fields: id:, name, tel_no, notes - invoices contains 9 fields: id, account_id, invoice_date, caller, date, hour, destination, total_duration,type_of_service

The total_duration field contains the information about the duration of a user call and it's expressed in seconds. The type_of_service field contains info about the destination of the call ("Land line, Another Net, Call Special Number").

I would like to be able to know the sum of minutes the user managed to accumulate when he made calls to "Land line, Another Net, Call Special Number".

What I have so far is:

$query=mysql_query("SELECT id,name,tel_no FROM users UNION SELECT id,account_id,caller FROM invoices WHERE caller LIKE \"%tel_no\"");

which gives me info about the tel numbers from the users table which are found in the invoices table ... but don't know how to get the total minutes used ...

Chris19
  • 210
  • 7
  • 17
  • It may not help answer your question, but you should stop using `mysql_*` functions. They're being deprecated. Instead use [PDO](http://php.net/manual/en/book.pdo.php) (supported as of PHP 5.1) or [mysqli](http://php.net/manual/en/book.mysqli.php) (supported as of PHP 4.1). If you're not sure which one to use, [read this SO article](http://stackoverflow.com/questions/13569/mysqli-or-pdo-what-are-the-pros-and-cons). – Matt Aug 10 '12 at 14:03

1 Answers1

1

Try using SUM:

$query=mysql_query("SELECT id,name,tel_no FROM users UNION SELECT id,account_id,caller, sum(total_duration) AS sum_total_duration FROM invoices WHERE caller LIKE \"%tel_no\"");

The value is accessed like so:

echo $returnedArray['sum_total_duration']
Austin
  • 6,026
  • 2
  • 24
  • 24
  • assuming the `total_duration` is in seconds and not like `08:13`. And you could use `sum(total_duration) AS total_duration` – Mihai Iorga Aug 10 '12 at 14:05
  • Good catch - however it _should_ be stored as seconds, by convention, just as all dates should be stored as DATETIME, etc. – Austin Aug 10 '12 at 14:06
  • Why dates should be stored as `timestamps` when you can use MySQL's `DATETIME` realy well? – Mihai Iorga Aug 10 '12 at 14:07
  • Typo, my point being that MySQL's features should be fully taken advantage of. – Austin Aug 10 '12 at 14:09
  • 1
    ahh .. ok .., then you can edit your answer to add `AS total_duration` :) – Mihai Iorga Aug 10 '12 at 14:10
  • Ahh... I knew I could use the `AS` operator here, just wasn't exactly sure on how... It's been a while since I've done any real PHP or MySQL. Python and MongoDB really differ from the former. – Austin Aug 10 '12 at 14:12
  • I'm sorry but I don't understand how to use the above code. Can you please be a bit more specific. Thanks. – Chris19 Aug 10 '12 at 15:12
  • I updated it to be more specific to your SQL. Note that this only works if total_duration values are stored as integers. – Austin Aug 10 '12 at 15:22
  • Well, doesn't really work ... if I use your code, I get the message "#1222 - The used SELECT statements have a different number of columns" and if I add another column in the first SELECT, I get the message "#1048 - Column 'id' cannot be null" ... thanks – Chris19 Aug 10 '12 at 18:52