PHP / MySQL question, more the latter

Question

So, I need some code help. I've got an awkward little MySQL table that just keeps growing and growing.

Let's assume that a set of clients, some in certain countries, that I need to 'total' the invoices for (need to calculate GST)

So, two tables contain the data I need -- one table has their ID and their country (In this case, Canada!). In our second table I've got their invoices, of which one variable is their ID from table1, and the another variable I've got to retrieve (and sum). Note it would help if they were summed into quarters. Invoices also have a duedate in the following syntax: 2006-06-26 (year-month-day).

In table 1 the ID set for each client is named: id -- it's numeric, e.g. 1-200000 In table 1 the country is: CA

In table 2 the variable for duedates is: duedate The other variable we require is the subtotal

Out of table2 I need to basically only grab whichever clients are in country CA (data from table1) and SUM their invoices.

The output should be as follows: 1st quarter sum: $x,xxx 2nd quarter sum: $x,xxx How?

Answer 1

SELECT SUM(subtotal) FROM table1, table2 WHERE table1.id=table2.table1_id AND country='CA' AND duedate > foo AND duedate < foo

This should be pretty self-explanatory. You join the two tables into one, combining rows by matching customer ID. Then you select only customers from country='CA'. Then you select only due dates in a certain range. Then you sum the invoice amounts. Kosher?

If you want to total the invoices by client, just SELECT table1.id in addition to the sum and add a GROUP BY table1.id to the end of the statement.

Answer 2

You can handle this in mysql entirely:

SELECT 
  CONCAT('Q', QUARTER(inv.duedate), ' ', YEAR(inv.duedate)) as quarter,
  SUM(inv.total) as total
FROM
  invoices inv
  LEFT JOIN clients cli ON inv.client_id = cli.id 
WHERE
  cli.country = 'CA'
GROUP BY
  quarter

Output:

+---------+--------+
| quarter | total  |
+---------+--------+
| Q1 2010 | 110.40 |
| Q2 2010 |  10.20 |
+---------+--------+
2 rows in set (0.00 sec)

My Test Data:

CREATE TABLE `clients` (
  `id` int(11) NOT NULL,
  `country` varchar(3) DEFAULT NOT NULL
);
INSERT INTO `clients` VALUES (1,'CA');

CREATE TABLE `invoices` (
  `client_id` int(11) NOT NULL,
  `duedate` date DEFAULT NOT NULL,
  `total` decimal(10,2) DEFAULT NOT NULL
);

INSERT INTO `invoices` VALUES (1,'2010-01-01','100.20'),(1,'2010-05-01','10.20'),(1,'2010-03-01','10.20');