Jump to content

How do I use MySQL Quarter() function


Landslyde
Go to solution Solved by Barand,

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?

Link to comment
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
Link to comment
Share on other sites

  • Solution

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
Link to comment
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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.