Count occurence of attribute values in each row with case-sensitive in sql

Question

I have table (notes_subject) structure -

+--------------+-------------+------+-----+---------+----------------+
| Field        | Type        | Null | Key | Default | Extra          |
+--------------+-------------+------+-----+---------+----------------+
| id           | int         | NO   | PRI | NULL    | auto_increment |
| user_id      | int         | NO   |     | NULL    |                |
| note_id      | varchar(25) | NO   | MUL | NULL    |                |
| subject_name | text        | NO   |     | NULL    |                |
+--------------+-------------+------+-----+---------+----------------+

and data stored in this table -

+----+---------+--------------+--------------+
| id | user_id | note_id      | subject_name |
+----+---------+--------------+--------------+
| 10 |       2 | UdMs870BSswp | CN           |
| 12 |       2 | 8stMvslwIGr2 | CN           |
| 13 |       2 | PB3KNbbFkaUm | cn           |
+----+---------+--------------+--------------+

Note : CN and cn (lowercase) are different.

I want to count the occurence of each subject_name by user_id in this table. So I run query -

SELECT subject_name, COUNT(subject_name) 
FROM notes_subject where user_id=2 GROUP BY subject_name;

and it fetched -

+--------------+---------------------+
| subject_name | COUNT(subject_name) |
+--------------+---------------------+
| CN           |                   3 |
+--------------+---------------------+

But this is not correct result because CN and cn are different.
and I also want id, user_id, note_id in the result.

This is probably related to the collation. I would suggest to search for something like [How to do a case sensitive GROUP BY?](https://stackoverflow.com/questions/10952696/how-to-do-a-case-sensitive-group-by). But that question is specific for SQL Server, so it might be different depending on your DB. — Hernán Alarcón, Aug 19 '20 at 02:53
@HernánAlarcón I have tried this query - `SELECT subject_name, COUNT(subject_name) FROM notes_subject GROUP BY subject_name COLLATE SQL_Latin1_General_CP1_CS_AS` but it is giving error — carl johnson, Aug 19 '20 at 02:55
Does https://stackoverflow.com/questions/5629111/how-can-i-make-sql-case-sensitive-string-comparison-on-mysql answer your question — Paul Baxter, Aug 19 '20 at 02:55
@HernánAlarcón `Unknown collation` error occured, I see the accepted answer in the link which you shared. He is giving **all values manually** of each rows. — carl johnson, Aug 19 '20 at 03:11

Radagast · Accepted Answer · 2020-08-19T03:15:59.393

1

If your database support window functions, I think you want something like this. Try with collate utf8mb4_bin to see if that helps with case sensitivity you need

select id, 
       user_id, 
       note_id, 
       subject_name, 
       count(subject_name collate utf8mb4_bin) over (partition by user_id, subject_name collate utf8mb4_bin) 
from notes_subject;

If window functions are out of question, you can also aggregate separately and join it back to the main table.

DEMO

edited Aug 19 '20 at 03:15

answered Aug 19 '20 at 02:56

Radagast

5,102
3
12
27

Unknown collation: 'Latin1_General_CS_AS' error occured. I am using MySql `8.0.21` in ubuntu. – carl johnson Aug 19 '20 at 03:15
@carljohnson It's because the solution was for SQL Server. Try now – Radagast Aug 19 '20 at 03:16
@carljohnson That I am not sure as I have never worked with one before – Radagast Aug 19 '20 at 03:54

Sujitmohanty30 · Answer 2 · 2020-08-19T04:20:57.593

0

Posting another alternative which is by selecting the group by column as a binary field.

Tested in dbfiddle with MYSQL 8.0 version

SELECT cast(subject_name as binary) subject_name, COUNT(subject_name)  cnt
  FROM notes_subject 
 WHERE user_id=2 
GROUP BY cast(subject_name as binary)

edited Aug 19 '20 at 04:20

answered Aug 19 '20 at 04:08

Sujitmohanty30

3,256
2
5
23

But what if I have tons of rows in DB, then I have to write `SELECT 10, 12, 13 ,14... `. – carl johnson Aug 19 '20 at 04:12
No No I just used CTE for demo purpose. just use like SELECT cast(subject_name as binary) subject_name, COUNT(subject_name) cnt FROM notes_subject WHERE user_id=2 GROUP BY cast(subject_name as binary) – Sujitmohanty30 Aug 19 '20 at 04:17
check the dbfiddle https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=a2f74cacd2440e963c68259a36025201 – Sujitmohanty30 Aug 19 '20 at 04:19
It is giving the value of subject_name as **0x434E** instead of CN and **0x636E** instead of cn. – carl johnson Aug 19 '20 at 04:33
I have provided the fiddle link with MYSQL 8.0 and its working. Not sure why it changes the value and may be because of any parameter setting on DB level. For the moment please ignore and go with the window clause solution. I will check and update if i found anything – Sujitmohanty30 Aug 19 '20 at 04:41

score 0 · Answer 3 · edited Aug 19 '20 at 04:52

0

You can simply use Binary to cast it.

SELECT count(*), CAST(subject_name as BINARY) AS lastname_cs 
FROM notes_subject where user_id=2 
GROUP BY CAST(subject_name as BINARY);

edited Aug 19 '20 at 04:52

carl johnson

415
3
10

answered Aug 19 '20 at 04:37

Atif

2,011
9
23

what is lastname_cs, I think it's a CPY post – carl johnson Aug 19 '20 at 04:45
thats the table I created on my local machine. – Atif Aug 19 '20 at 05:03

Count occurence of attribute values in each row with case-sensitive in sql

3 Answers3