72946

How to get the count of each distinct value in Multiple columns and get the result in separate colum

Question:

I need the following table to be queried to get the result given below the table.

Table:

---------------------------------- | Name | Age | slot | |-------|--------|---------------| |A |20 | 1 | |B |30 | 2 | |C |30 | 1 | |D |20 | 1 | |E |40 | 2 | |F |40 | 3 | |G |50 | 3 | ----------------------------------

Result:

------------------------------------------- |Age |Age_Count |Slot |Slot_Count| ------------------------------------------- |20 | 2 |1 |3 | ------------------------------------------- |30 | 2 |2 |2 | ------------------------------------------- |40 | 2 |3 |2 | ------------------------------------------- |50 | 1 | -----------------------

While searching stackoverflow i found <a href="https://stackoverflow.com/questions/7053902/sql-how-to-get-the-count-of-each-distinct-value-in-a-column" rel="nofollow">this question for single column</a> question and there is [this link for multiple columns] (<a href="https://stackoverflow.com/questions/28133875/get-the-count-of-each-distinct-value-in-multiple-columns" rel="nofollow">get the count of each distinct value in "Multiple" columns</a>) question. The answers from the second link (for the multiple coulmn's distinct count) is displayed under a single column and my requirement is i guess quite different from the answers posted there.

Thanks in advance

Answer1:

Your request is kind of odd. Are you sure you want that?

If so, this may help:

SET @x:=0,@y:=0,@m:=0,@n:=0; SELECT DISTINCT age,age_count, slot,slot_count FROM ( SELECT age, age_count, slot, slot_count FROM ( SELECT @x:=@x + 1 AS aid, age, COUNT(*) age_count FROM slots GROUP BY age ) a LEFT JOIN ( SELECT @y:=@y + 1 AS sid, slot, COUNT(*) slot_count FROM slots GROUP BY slot ) s ON a.aid = s.sid UNION SELECT age, age_count, slot, slot_count FROM ( SELECT @m:=@m + 1 AS aid, slot, COUNT(*) slot_count FROM slots GROUP BY slot ) a LEFT JOIN ( SELECT @n:=@n + 1 AS sid, age, COUNT(*) age_count FROM slots GROUP BY age ) s ON a.aid = s.sid ) a

If you know for sure that you have more unique ages than unique slots , or opposite, you can get ride of messy union.

Recommend

  • Using SUMIF or SUMIFS in excel sheet
  • Android Room - reset auto generated key on each app run
  • non-negative integer solutions to system of linear equations in mathematica
  • SQL Count. How can I count how many distinct values are in a table when an other two columns are mat
  • SQL duplicate rows with multiple left joins
  • JPA - getting distinct value from one column
  • How to translate SQL queries to cypher in the optimal way?
  • Add delivery info to query in SAP Crystal Reports
  • How to notify a specific thread in Java
  • Taking mean across rows grouped by a variable in numpy
  • Grouping by blank nodes
  • Redis under Classic ASP(VBScript)
  • Query to get the Top 2 from each group
  • How to implement limit with Nhibernate and Sybase
  • Primefaces :radioButton inside a ui:repeat
  • Find group of records that match multiple values
  • R convert summary result (statistics with all dataframe columns) into dataframe
  • Using Sax parsing to edit and write XML in VB6
  • Ember.js model to be organised as a tree structure
  • Breaking out column by groups in Pandas
  • Unable to get column index with table.getColumn method using custom table Model
  • Groovy: Unexpected token “:”
  • Webgrid not refreshing after delete MVC
  • AJAX Html Editor Extender upload image appearing blank
  • Replace value with Factor in r data.table
  • Jquery UI tool tip close icon
  • Django: Count of Group Elements
  • How to access EntityManager inside Entity class in EJB3
  • Repeat a vertical line on every page in Report Builder / SSRS
  • Sails.js/waterline: Executing waterline queries in toJSON function of a model?
  • Android screen density dpi vs ppi
  • Bug in WPF DataGrid
  • Window Size for Mac application
  • Sending data from AppleScript to FileMaker records
  • MySQL WHERE-condition in procedure ignored
  • Redux, normalised entities and lodash merge
  • Do create extension work in single-user mode in postgres?
  • Traverse Array and Display in markup
  • Qt: Run a script BEFORE make
  • java string with new operator and a literal