MySQL - Group by multiple columns from same table

Question

I have a table similar to the following

sno | booking_id | room_type | gender | age | amount | days
1   | 2016JUL001 | AC        | Male   | 25  | 1000   | 15 
2   | 2016JUL001 | AC        | Male   | 42  | 1000   | 15 
3   | 2016JUL001 | AC        | Male   | 28  | 1000   | 15 
4   | 2016JUL010 | N AC      | Female | 45  | 1000   | 15 
5   | 2016JUL010 | N AC      | Female | 46  | 1000   | 15 
6   | 2016JUL005 | N AC      | Male   | 28  | 1000   | 15 
7   | 2016JUL005 | N AC      | Female | 35  | 1000   | 15 
8   | 2016JUL009 | AC        | Female | 26  | 1000   | 15 
9   | 2016JUL009 | AC        | Female | 25  | 1000   | 15

... so on

Expected output [If I want to get gender='Female']

sno | booking_id | room_type | gender | age | amount | days
4   | 2016JUL010 | N AC      | Female | 45  | 1000   | 15 
5   | 2016JUL010 | N AC      | Female | 46  | 1000   | 15
8   | 2016JUL009 | AC        | Female | 26  | 1000   | 15 
9   | 2016JUL009 | AC        | Female | 25  | 1000   | 15

Expected output [If I want to get gender='Male']

sno | booking_id | room_type | gender | age | amount | days
1   | 2016JUL001 | AC        | Male   | 25  | 1000   | 15 
2   | 2016JUL001 | AC        | Male   | 42  | 1000   | 15 
3   | 2016JUL001 | AC        | Male   | 28  | 1000   | 15

Expected output [If I want to get gender='Male' AND gender='Female']

sno | booking_id | room_type | gender | age | amount | days
6   | 2016JUL005 | N AC      | Male   | 28  | 1000   | 15 
7   | 2016JUL005 | N AC      | Female | 35  | 1000   | 15

NOTE: I want 3 separate individual QUERIES to get the above outputs

Thanks in advance

Please read the complete post, I have mentioned the 3 expected outputs and the query to be modified @sagi — Sathvik C
– Sathvik C, Commented Aug 22, 2016 at 11:03

sagi · Accepted Answer · 2016-08-22 11:25:03Z

3

First query :

SELECT sno, booking_id, room_type, gender, age 
FROM customer_data 
WHERE booking_id IN ( SELECT booking_id FROM customer_data 
                      WHERE gender='female' AND age>0 and RIGHT(booking_id,1) <> '1' 
                      GROUP BY booking_id HAVING COUNT(*) > 1 ) 
ORDER BY booking_id ASC, age ASC

Second :

SELECT sno, booking_id, room_type, gender, age 
FROM customer_data 
WHERE booking_id IN ( SELECT booking_id FROM customer_data 
                      WHERE gender='male' AND age>0 
                      GROUP BY booking_id HAVING COUNT(*) > 1 ) 
ORDER BY booking_id ASC, age ASC

And third:

SELECT sno, booking_id, room_type, gender, age 
FROM customer_data 
WHERE booking_id IN ( SELECT booking_id FROM customer_data 
                      WHERE gender IN('male','female') AND age>0 
                      GROUP BY booking_id HAVING COUNT(distinct gender) = 2 ) 
ORDER BY booking_id ASC, age ASC

If in the first two you wanted only booking_id that has only 1 gender, add to the having clause :

AND COUNT(distinct gender) = 1

edited Aug 22, 2016 at 11:25

answered Aug 22, 2016 at 11:06

sagi

40.6k7 gold badges66 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

Sathvik C Over a year ago

Booking ID's are not same as I mentioned.. It will change

sagi Over a year ago

Will change how? What's wrong? Are you expecting me to guess what you need?

sagi Over a year ago

Thanks. @Manishsharma

Sathvik C Over a year ago

I mean there can be other bookings will have the number 1. you have mentioned booking_id NOT LIKE '%1%' in the query. The original format of booking id is YEARMONTHNUMBER [2016AUG0001]

Sathvik C Over a year ago

Sorry for that, I mean to say is that will keep on increment and I have lots of rows and don't mention the booking id in such way.

|

Sathvik C · Accepted Answer · 2016-08-23 09:29:08Z

1

After lot of tries, I am able to get the data I want

Query ['Female']

SELECT sno, bd.booking_id, bd.room_type, bd.gender, bd.age 
FROM customer_data bd 
INNER JOIN ( 
    SELECT booking_id, GROUP_CONCAT(DISTINCT gender) AS g 
    FROM customer_data 
    WHERE gender!='' AND age>0 
    GROUP BY booking_id 
    HAVING COUNT(booking_id) > 1 
    ORDER BY booking_id ASC, gender DESC 
) cbd 
WHERE cbd.booking_id = bd.booking_id AND cbd.g = 'Female'

Query ['Male']

SELECT sno, bd.booking_id, bd.room_type, bd.gender, bd.age 
FROM customer_data bd 
INNER JOIN ( 
    SELECT booking_id, GROUP_CONCAT(DISTINCT gender) AS g 
    FROM customer_data 
    WHERE gender!='' AND age>0 
    GROUP BY booking_id 
    HAVING COUNT(booking_id) > 1 
    ORDER BY booking_id ASC, gender DESC 
) cbd 
WHERE cbd.booking_id = bd.booking_id AND cbd.g ='Male'

Query ['Male and Female']

SELECT sno, bd.booking_id, bd.room_type, bd.gender, bd.age 
FROM customer_data bd 
INNER JOIN ( 
    SELECT booking_id, GROUP_CONCAT(DISTINCT gender ORDER BY gender DESC) AS g 
    FROM customer_data 
    WHERE gender!='' AND age>0 
    GROUP BY booking_id 
    HAVING COUNT(booking_id) > 1 
    ORDER BY booking_id ASC, gender DESC 
) cbd 
WHERE cbd.booking_id = bd.booking_id AND cbd.g = 'Male,Female'

edited Aug 23, 2016 at 9:29

answered Aug 23, 2016 at 7:51

Sathvik C

6121 gold badge9 silver badges23 bronze badges

4 Comments

sagi Over a year ago

Looks very inefficient . A) Replace LIKE with = when looking for perfect match. B) You can order by the GROUP_CONCAT() so it will always be the same order and you won't have to look both Male,Female and Female,Male . Look at my having clause, you can add it in the inner query , add to the having clause HAVING MAX(gender) = 'female' for only females , HAVING MIN(gender) = 'man' for mens. and HAVING COUNT(DISTINCT GENDER) = 2 for both.

Sathvik C Over a year ago

Yeah I have already optimized in my code @sagi. Thank you, I have answered because some other will get some idea from this. Anyways, I will also edit here.

Sathvik C Over a year ago

I think HAVING MAX(gender) = 'female' this will not work in some cases

sagi Over a year ago

We are talking about your scenario , only man,female available. So if MAX returns female and not male(since male is larger than female on ASCI table) that it must be the only option. Same goes for man.

Strawberry · Accepted Answer · 2016-08-22 11:55:49Z

0

Your schema appears somewhat flawed. Nevertheless, here's something to think about...

SELECT booking_id
     , COUNT(DISTINCT gender) x 
  FROM customer_data 
 WHERE gender IN ('Male','Female') <-- not strictly necessary if there are only two genders.
 GROUP 
    BY booking_id;

answered Aug 22, 2016 at 11:55

Strawberry

34k14 gold badges43 silver badges58 bronze badges

2 Comments

sagi Over a year ago

By his query, I assume that there is also an empty string value for gender.

Strawberry Over a year ago

@sagi Yes, I think you're right - so it is necessary in this instance

Collectives™ on Stack Overflow

MySQL - Group by multiple columns from same table

3 Answers 3

11 Comments

4 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

11 Comments

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related