Question 356263 in Mysql Question posted in
The official MySQL documentation can be found here.

Finding the names of columns with a common value in MySQL

I have a table named ‘fiction’ created in MySQL. The table looks like this

fiction:

id name gender image browser os
1 chris male 1 0 ios
2 emma female 1 1 ios
3 james male 0 0 android
4 lucas male 1 0 android
5 amelia female 0 1 ios
6 olivia female 1 0 ios

What I want to do is to retrieve the names of the columns where the records in the ‘gender’ column have the value ‘male’, and all other columns have the same value. For example, If I run this code:

SELECT * FROM fiction WHERE gender = "male";

The output will be:

id name gender image browser os
1 chris male 1 0 ios
3 james male 0 0 android
4 lucas male 1 0 android

As you can see, the ‘browser’ column has a common value of ‘0’ in all records. So I want this column name, ‘browser’. Another example:

SELECT * FROM fiction WHERE gender = "female";

Output:

id name gender image browser os
2 emma female 1 1 ios
5 amelia female 0 1 ios
6 olivia female 1 0 ios

As you can see here, the ‘os’ column has a common value in all of them. I need an SQL statement that will show me these common columns.

I used GROUP BY and HAVING expressions, but as you can guess, they didn’t solve my problem. I think I need to do something related to the information_schema table.

Tags:

3

Answers


  1. 0

    As you don’t explain what exact outcome you want, i show you for an example how to do it and then you can extend it to your purposes

    Then basic idea is that the group can have only the count of distinct values of 1, if you are searching for common values

    The query bleow finds also all common columns

    WITH cnt as (
  SELECT 
  COUNT( DISTINCT `image`) img,
  COUNT(DISTINCT `os`) osc
  , COUNT(DISTINCT `browser`)  brws
  FROM fiction WHERE gender = "male"
  )
SELECT
 `id`, `name`, `gender`,
  GROUP_CONCAT(
  CASE WHEN brws = 1 THEN 'browser' ELSE '' END ,
  CASE WHEN osc = 1 THEN 'ios'  ELSE '' END ,
  CASE WHEN img = 1 THEN 'image'  ELSE ''  END ) gc

FROM fiction CROSS JOIN cnt
WHERE gender = "male"
  GROUP BY `id`, `name`, `gender`
    id name gender gc
    1 chris male browser
    3 james male browser
    4 lucas male browser

    fiddle

    Login or Signup to reply.
  2. 0

    To give you an idea:

    Count distinct values, and if it is equal to 1, then returns desired name (column name for your case), and if it is not returns "-". Finally we add the outputs together.

    SELECT CONCAT(
  IF((count(distinct(os))=1), "os", '-'),
  ',',
  IF((count(distinct(browser))=1), "browser", '-'),
  ',',
  IF((count(distinct(image))=1), "image", '-')
) FROM FICTION WHERE gender = "female";

    The output is: os,-,-

    If you are sure that you have only one common column, you can change "-" with an empty string and remove the ‘,’ from CONCAT. So query will be like:

    SELECT CONCAT(
   IF((count(distinct(os))=1), "os", ''),
   IF((count(distinct(browser))=1), "browser", ''),
   IF((count(distinct(image))=1), "image", '')
) FROM FICTION WHERE gender = "female";

    And the ouput is: os.

    Login or Signup to reply.
  3. 0 
    SELECT *
FROM (
   SELECT 'name' col, name, count(*) c FROM mytable WHERE gender = 'male' group by name union all
   SELECT 'gender', gender, count(*) c FROM mytable WHERE gender = 'male' group by gender union all
   SELECT 'image', image, count(*) c FROM mytable WHERE gender = 'male' group by image union all
   SELECT 'browser', browser, count(*) c FROM mytable WHERE gender = 'male' group by browser union all
   SELECT 'os', os, count(*) c FROM mytable WHERE gender = 'male' group by os 
   
) x
WHERE x.c = (SELECT count(*) FROM mytable WHERE gender = 'male');
;

    Solution for female is the same, just replace ‘mail’ by ‘female’.

    see: DBFIDDLE

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search