Compare number of credits users using two different tables in MySql 8

Question

On my MySql version 8.0.17 database I have two different tables.

In the table table_credits_used I stored for each user the number of access in shared area for current date.

mysql> SELECT
       COUNT(*) tCount,
       tNameUser
FROM
    `table_credits_used` 
WHERE
    tDate = CURDATE() 
GROUP BY
    tDate,
    tNameUser;

+--------+-----------+
| tCount | tNameUser |
+--------+-----------+
|      1 | Chenoa    |
|      6 | Kimi      |
|      1 | Aponi     |
|      1 | Imala     |
|      6 | Doba      |
|      1 | Elsu      |
|      1 | Jair      |
|      2 | Nova      |
|      1 | Aarav     |
|      1 | Aarush    |
|      1 | Aaryan    |
|      1 | Aayansh   |
|      1 | Aayush    |
|      5 | Abeer     |
|      1 | Adhrit    |
|      2 | Adi       |
|      1 | Aditya    |
|      1 | Advaith   |
|      6 | Advay     |
|      6 | Advik     |
|      6 | Agastya   |
+--------+-----------+
21 rows in set (0.04 sec)

In the table table_credit_to_use is stored for each user the maximum number of access in shared area for current date.

mysql> SELECT * FROM `table_credit_to_use`;
+--------+-----------+-----+
| tCount | tNameUser | tID |
+--------+-----------+-----+
|      1 | Chenoa    |   1 |
|      6 | Kimi      |   2 |
|      1 | Aponi     |   3 |
|      1 | Imala     |   4 |
|      6 | Doba      |   5 |
|      1 | Elsu      |   6 |
|      1 | Jair      |   7 |
|      2 | Nova      |   8 |
|      1 | Aarav     |   9 |
|      1 | Aarush    |  10 |
|      1 | Aaryan    |  11 |
|      1 | Aayansh   |  12 |
|      1 | Aayush    |  13 |
|      6 | Abeer     |  14 |
|      1 | Adhrit    |  15 |
|      2 | Adi       |  16 |
|      1 | Aditya    |  17 |
|      1 | Advaith   |  18 |
|      6 | Advay     |  19 |
|      6 | Advik     |  20 |
|      6 | Agastya   |  21 |
+--------+-----------+-----+
21 rows in set (0.04 sec)

I need to show users who have not consumed the maximum number of logins for the current date.

In this example, I need this output:

+--------+-----------+-----+
| tCount | tNameUser | tID |
+--------+-----------+-----+
|      5 | Abeer     |  14 |
+--------+-----------+-----+

Because the user Abeer has the right to 6 total accesses, but today he has used only 5.

Any suggestion?

My table structure below on db-fiddle.com, which offers MySQL 8.0

score 1 · Answer 1 · answered Aug 21 '21 at 16:43

1

Your sample data has no tdate

But you can join the table with the subselect

SELECT 
t1.tNameUser,t1.tCount,t2.tCount current_count
FROM `table_credit_to_use` t1 
INNER JOIN ( SELECT
       COUNT(*) tCount,
       tNameUser
FROM
    `table_credits_used` 
WHERE
    tDate = CURDATE() 
GROUP BY
    tDate,
    tNameUser) t2 ON t1.tNameUser  = t2.tNameUser

answered Aug 21 '21 at 16:43

nbk

45,398
8
30
47

thank you for reply, really appreciated. Please see https://www.db-fiddle.com/f/8Z7ETjkDKtpXXpy9sPKggQ/3 the output is wrong. The field `tdate` I do not use it – Edward Sheriff Curtis Aug 21 '21 at 16:54
so i tried iz and it looks ok to me https://www.db-fiddle.com/f/8Z7ETjkDKtpXXpy9sPKggQ/9 the numbers are ok – nbk Aug 21 '21 at 17:04

score 1 · Accepted Answer · answered Aug 21 '21 at 16:58

1

Make sure you have you have ONLY_FULL_GROUP_BY disabled in MySQL.

mysql> SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

The following SQL should do the trick:

SELECT cu.tCount, cu.tNameUser, cu.tID FROM table_credits_used cu
INNER JOIN table_credit_to_use ctu ON (ctu.tID = cu.tID)
WHERE cu.tCount < ctu.tCount
AND cu.tDate = CURDATE() 
GROUP BY cu.tDate, cu.tNameUser;

Note: The ONLY_FULL_GROUP_BY is enabled on the dbfiddle hence I couldn't try the above full query. The one that worked in db fiddle is the following without the group by clause:

SELECT cu.tCount, cu.tNameUser, cu.tID FROM table_credits_used cu
INNER JOIN table_credit_to_use ctu ON (ctu.tID = cu.tID)
WHERE cu.tCount < ctu.tCount;

answered Aug 21 '21 at 16:58

Salvino D'sa

4,018
1
7
19

Many thanks for help, really appreciated. Your suggestion working. Please see https://www.db-fiddle.com/f/8Z7ETjkDKtpXXpy9sPKggQ/8 – Edward Sheriff Curtis Aug 21 '21 at 17:02
Cool, happy coding! – Salvino D'sa Aug 21 '21 at 17:03
please **never disable** ONLY_FULL_GROUP_BY – nbk Aug 21 '21 at 17:09
@nbk we have had situations in past where putting the entire select clause in group by wasn't really feasible. So we disabled `ONLY_FULL_GROUP_BY` on our servers. It's no harm if you know the consequences of it and why MySQL has enabled that flag in the first place by default. So yes, you can always toggle it, based on the needs of the project. – Salvino D'sa Aug 21 '21 at 17:12
result sets in sql are unsorted so you can get unwanted results, that is a fact of life and that is why this should always be enalbled to get people to program clean. i wished mysql would makle the same as sql server and not allow that ... – nbk Aug 21 '21 at 17:16
I understand your thought process. But sometimes it's not really possible when you have complex logic/calculations in your SQL. That's why this [answer](https://stackoverflow.com/questions/23921117/disable-only-full-group-by) has a lot of up votes :-) – Salvino D'sa Aug 21 '21 at 17:20
oh yes i have queries that are huge on huge databases and it is always feasible to make that with full group by. as i said result sets are unsiorted and canfive you any kind of response – nbk Aug 21 '21 at 17:40

Compare number of credits users using two different tables in MySql 8

2 Answers2