1

table1

ID | Name
1    ID1
2    ID2
3    ID3

table2

ID | table1ID | Values
1    1          3 
2    2          3
3    3          2
4    1          3
5    2          1
6    3          2
7    1          1
8    2          3
9    2          2
10   1          1
11   2          1

I need as columns, table1s name, table2s Count of Values being 1, values = 2 and values = 3. These also need to be sorted by the total count of values = 1 followed by total count of values = 2

So Basically

Name | 1s | 2s | 3s
ID2    2    1    2
ID1    2    0    2
ID3    0    2    0

My existing code is

$sql2 = "SELECT * FROM table1
INNER JOIN table2 b ON 
(table1.ID= b.table1ID) 
WHERE b.Values = '1'
GROUP BY b.table1ID
ORDER BY count(*) DESC";

Works for the single sort by but not the second.

1

i would be tempted to write something like below to pivot sum.

Query

SELECT 
   table1.Name 
 , SUM(CASE WHEN table2.Values = 1 THEN 1 ELSE 0 END) AS "s1"
 , SUM(CASE WHEN table2.Values = 2 THEN 1 ELSE 0 END) AS "s2"
 , SUM(CASE WHEN table2.Values = 3 THEN 1 ELSE 0 END) AS "s3"
FROM 
 table1
INNER JOIN
 table2 
ON
 table1.ID = table2.table1ID
GROUP BY 
 table1.Name 
| improve this answer | |
  • 1
    "VALUES is a reserved word. I suggest giving the column a name that reflects what it is a value of." @Lennart very true but in some MySQL versions if you use alias or fully quantified table names for reserved word the optimizer should see the reserved word should be used as a column name consider this vs this .. But i totally agree ideally you should not be using SQL standard reserved words – Raymond Nijland Jun 21 '19 at 9:45
  • 1
    Let us continue this discussion in chat. – Lennart Jun 21 '19 at 13:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.