How to use sql GROUP BY clause on two columns?

Question

I have 2 Mysql tables :

station :

logger :

Using this sql query it's showing following result :

$getStation = mysqli_query($conn, "SELECT st.st_tbl_id, st.st_name, st.st_address, st.st_lat, st.st_long, st.created,
GROUP_CONCAT(lg.lg_name) AS lg_name 
FROM station AS st 
LEFT JOIN logger AS lg ON lg.lg_id = st.lg_id 
GROUP BY st.st_name, st.st_id, st.lg_id 
ORDER BY st.st_tbl_id DESC");

Here you can see station name is duplicate value but it's should be unique one. Like for this scenario it's should be Comilla and Dhaka

Your query and results doesn't match. Try to add the proper query and the actual result. — Jibin Balachandran, Dec 03 '16 at 06:41
The logger name is different for the same station name, that's why it's not grouped properly.If u don't want duplicate station name, remove the loggername from `select` and `group by`. — Jibin Balachandran, Dec 03 '16 at 06:45
please provide sample data using sql format then it would be great and give you right solution within a short time. — Faisal, Dec 03 '16 at 07:08
@shibbirahmed The loggerName is different for all of them, remove it in select command — javad, Dec 03 '16 at 08:38
@shibbirahmed each person have 2 loggerNames, so you can't do this — javad, Dec 03 '16 at 09:35
well, there 3 tables. 1) station 2) logger 3) channel.. Each station can add multiple logger and each logger can add multiple channel. — shibbir ahmed, Dec 03 '16 at 09:36

score 0 · Answer 1 · edited May 23 '17 at 10:30

0

See https://stackoverflow.com/a/2421441/347565

In your case, those entries have different lg_id, so they won't be grouped together. I think you just change your query to only group by st.st_id and you'll get the results you want.

edited May 23 '17 at 10:30

Community

1
1

answered Dec 03 '16 at 06:42

Matthieu

16,103
10
59
86

If I use only `st.st_id` then all duplicate `lg_name` is showing – shibbir ahmed Dec 03 '16 at 06:47
Can you update your question with what you get if you only 'group by st.st_id'. I'm pretty sure you would get only one row per st_id...isn't that what you want? – Matthieu Dec 03 '16 at 06:51
If I use group by `st.st_id` then `st_name` is showing unique value but `lg_name` is showing duplicate value. – shibbir ahmed Dec 03 '16 at 06:52

Jibin Balachandran · Answer 2 · 2016-12-03T07:04:40.337

0

Try this:

$getStation = mysqli_query($conn, "SELECT st.st_name, st.st_address, st.st_lat, st.st_long, st.created,
GROUP_CONCAT(lg.lg_name) AS lg_name 
FROM station AS st 
LEFT JOIN logger AS lg ON lg.lg_id = st.lg_id 
GROUP BY st.st_name,st.st_address,st.st_lat, st.st_long, st.created");

edited Dec 03 '16 at 07:04

answered Dec 03 '16 at 06:52

Jibin Balachandran

3,381
1
24
38

same result like mine. – shibbir ahmed Dec 03 '16 at 06:53
Yes dude I did it – shibbir ahmed Dec 03 '16 at 06:56
Your latest updated code is showing duplicate lg_name. – shibbir ahmed Dec 03 '16 at 07:03
Sorry, I forgot to remove `table_id` from `order by`.. Updated it again.. let me know in case it gives any problem – Jibin Balachandran Dec 03 '16 at 07:04
Undefined index: st_tbl_id in 2 times. – shibbir ahmed Dec 03 '16 at 07:24
Dude, You above query is showing 2 error which is Undefined index: st_tbl_id and showing duplicate lg_name. – shibbir ahmed Dec 03 '16 at 09:19

How to use sql GROUP BY clause on two columns?

2 Answers2