How do I use MySQL Quarter() function

[Landslyde]

I'm failing miserably at this, but here's what I have:

MariaDB [master]> describe quarters;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| qtr   | int(11) | NO   | PRI | NULL    |       |
+-------+---------+------+-----+---------+-------+

MariaDB [master]> describe history;
+--------------+------------------+------+-----+---------+----------------+
| Field        | Type             | Null | Key | Default | Extra          |
+--------------+------------------+------+-----+---------+----------------+
| historyid    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| amount       | float            | NO   |     | NULL    |                |
| subsidy      | char(1)          | NO   |     | NULL    |                |
| last_payment | date             | NO   |     | NULL    |                |
| amount_paid  | float            | NO   |     | NULL    |                |
| balance      | float            | NO   |     | NULL    |                |
| attend       | char(1)          | NO   |     | N       |                |
| attend_date  | date             | NO   |     | NULL    |                |
| groupid      | int(11) unsigned | NO   |     | NULL    |                |
| clientid     | int(10) unsigned | NO   | MUL | NULL    |                |
| memberid     | int(10) unsigned | NO   | MUL | NULL    |                |
+--------------+------------------+------+-----+---------+----------------+

MariaDB [master]> Select clientid, last_payment, amount_paid
    -> From history
    -> WHERE YEAR(last_payment) = YEAR(CURDATE())
    -> AND memberid = "1";
+----------+--------------+-------------+
| clientid | last_payment | amount_paid |
+----------+--------------+-------------+
|        3 | 2016-01-26   |         100 |
|        3 | 2016-10-29   |          15 |
|        3 | 2016-01-30   |          15 |
|        4 | 2016-01-26   |          30 |
|        4 | 2016-10-29   |          30 |
|        4 | 2016-01-30   |          15 |
|        1 | 2016-01-26   |          15 |
|        1 | 2016-10-29   |          30 |
|        1 | 2016-01-30   |         100 |
|        2 | 2016-01-26   |          30 |
|        2 | 2016-10-29   |          30 |
|        2 | 2016-01-30   |          30 |
|        3 | 2016-01-28   |         100 |
|        4 | 2016-01-30   |          15 |
|        1 | 2016-01-30   |         100 |
|        2 | 2016-01-30   |          30 |
|        5 | 2016-01-29   |          30 |
|        5 | 2016-02-02   |          30 |
|        3 | 2016-02-02   |          15 |
|        4 | 2016-02-02   |          30 |
|        1 | 2016-02-02   |          15 |
|        2 | 2016-02-02   |          30 |
+----------+--------------+-------------+

There's returnable data from `amount_paid`, but the following query returns 0 rows.

MariaDB [master]> SELECT qtr AS Quarter, SUM(amount_paid) as Total
    -> FROM quarters q
    -> LEFT JOIN history h ON qtr = QUARTER(last_payment)
    -> WHERE YEAR(last_payment) = YEAR(CURDATE())
    -> AND memberid = "1"
    -> GROUP BY qtr;
Empty set (0.01 sec)

For the love of Mary! What am I doing wrong?

[Barand]

Do you have a record for each quarter in the quarters table?

 

And as you are left joining to the history table, put those conditions in the ON clause instead of the WHERE clause (ie change "WHERE" to "AND" in your query)

[Landslyde]

Barand:

 

Truth is, another person helped me with this. The query, the quarters table, all from him. But that was some time back and I can't recall who it was or what forum it was on for me to go back and review my history. This use to work, but somehow I've fouled it up.

 

There will be many members, so having the quarters table store quarterly info isn't an option. Each member must be able to see their own quarterly earnings. 

 

As you suggested, I changed the query to:

$stmt = $db->prepare('SELECT qtr AS Quarter, SUM(amount_paid) as Total
			FROM quarters AS q
			LEFT JOIN history AS h ON YEAR(last_payment) = YEAR(CURDATE())
			WHERE qtr = QUARTER(last_payment)
			AND memberid = :memberid
			GROUP BY qtr');
$stmt->bindValue(':memberid', $_SESSION["memberid"], PDO::PARAM_INT);

but this provided nothing. Even the LEFT JOIN's ON clause looks strange to me. If you decide to help me further, would you also briefly explain the query? I have almost 50 working pages full of queries a lot larger than this, all done by me, but this one...this one's kicking me good. I just don't get it. Thanks.

Edited February 9, 2016 by Landslyde

[Barand]

my data

mysql> SELECT * FROM history;
+-----------+----------+--------------+-------------+
| historyid | memberid | last_payment | amount_paid |
+-----------+----------+--------------+-------------+
|         1 |        1 | 2015-12-20   |     1000.00 |
|         2 |        1 | 2016-01-01   |      150.00 |
|         3 |        1 | 2016-01-10   |       50.00 |
|         4 |        1 | 2016-02-03   |       60.00 |
|         5 |        1 | 2016-02-25   |       40.00 |
|         6 |        1 | 2016-05-30   |      300.00 |
|         7 |        1 | 2016-06-22   |      200.00 |
|         8 |        2 | 2016-01-05   |      155.00 |
|         9 |        2 | 2016-02-14   |      205.00 |
+-----------+----------+--------------+-------------+

mysql> SELECT * FROM quarters;
+-----+
| qtr |
+-----+
|   1 |
|   2 |
|   3 |
|   4 |
+-----+

the query and results - I told you to put the conditions on the history table into the ON clause, not in the WHERE clause.

mysql> SELECT qtr AS Quarter, SUM(amount_paid) as Total
    -> FROM quarters q
    -> LEFT JOIN history h ON qtr = QUARTER(last_payment)
    ->     AND YEAR(last_payment) = YEAR(CURDATE())
    ->     AND memberid = '1'
    -> GROUP BY qtr;
+---------+--------+
| Quarter | Total  |
+---------+--------+
|       1 | 300.00 |
|       2 | 500.00 |
|       3 |   NULL |
|       4 |   NULL |
+---------+--------+

If you have them in the WHERE clause then the LEFT JOIN behaves as as an INNER JOIN like this

mysql> SELECT qtr AS Quarter, SUM(amount_paid) as Total
    -> FROM quarters q
    -> LEFT JOIN history h ON qtr = QUARTER(last_payment)
    -> WHERE YEAR(last_payment) = YEAR(CURDATE())
    ->     AND memberid = '1'
    -> GROUP BY qtr;
+---------+--------+
| Quarter | Total  |
+---------+--------+
|       1 | 300.00 |
|       2 | 500.00 |
+---------+--------+

[Landslyde]

I appreciate you, Barand. Every time I think I have a good grasp of the LEFT JOIN, something like this shows me I'm still on a slippery slope. I understand your explanation. And even more than the working solution you provided, the explanation was what I was looking for. Something more to help me try to solidify LEFT JOINS. Thank you for taking time to offer that. It helped.