Jump to content
Landslyde

How do I use MySQL Quarter() function

Recommended Posts

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?

Share this post


Link to post
Share on other sites

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)

Share this post


Link to post
Share on other sites

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 by Landslyde

Share this post


Link to post
Share on other sites

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 |
+---------+--------+
  • Like 1

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.