-1

I have 3 tables that look like this:

Table1

ID    x     y      z    
1     0     30     0     
2    60      0     0     
3    10     30     0     
4    30     30    30 



Table2

ID    x       y      z    
1     0       0     50  
2     0      10      0     
3    10      30      0     
4     0       0      0 


Table3

ID     x      y      z    
1     20     30      0     
2      0      0     40     
3      0     30      0     
4     30      0     20

I want to be able to query this and return the ID with the number of columns that have more than 0 as their value in that row. So the result would look like this:

ID    Count
1      4
2      3
3      5
4      5
Strawberry
  • 33,750
  • 13
  • 40
  • 57
Jerry Myrrh
  • 3
  • 1
  • 3
    What is the difference between the tables? Because from what i see normalisation into one table is the best option it also makes writting a query much more easy – Raymond Nijland Oct 26 '18 at 15:49
  • I want to be able to query this and return the ID with the number of columns that have more than 0 as their value in that row. So the result would look like this: ID Count 1 4 2 3 3 5 4 5 – Jerry Myrrh Oct 26 '18 at 15:49
  • Agree. Normalise your schema – Strawberry Oct 26 '18 at 15:51
  • there is no diference but the they all take data from different sources – Jerry Myrrh Oct 26 '18 at 15:54
  • Have one table with an extra column: source – Strawberry Oct 26 '18 at 15:55
  • "there is no diference but the they all take data from different sources" make two tables one table with `id`, `source` columns and one table with `id, x, y, z, source_id` columns – Raymond Nijland Oct 26 '18 at 15:55
  • @JayBlanchard atleast duplicate to a MySQL syntax union version.. Topicstarter `[C:\DB\DB1.mdb]` with in the duplicated link isn't valid MySQL syntax so you can't use that in MySQL.. Brackets are SQL Server (MSSQL) only.. The rest is valid MySQL syntax.. – Raymond Nijland Oct 26 '18 at 16:05
  • My bad @RaymondNijland, was doing it from my phone and my fat old fingers chose the wrong one. – Jay Blanchard Oct 26 '18 at 16:06
  • No problem @JayBlanchard those things happen.. i have atleast update mine comment so it contains some explaination about the duplication link.. I geuss the topicstarter should understand those to get a query going.. – Raymond Nijland Oct 26 '18 at 16:10

1 Answers1

0

This is ugly, but this is simple and should do what you are asking for.

select id, sum(case when x > 0 then 1 else 0 end + case when y > 0 then 1 else 0 end + case when z > 0 then 1 else 0 end) count
from (
select id, x, y, z from table1
union all
select id, x, y, z from table2
union all
select id, x, y, z from table3) tables
group by id;
DanB
  • 2,022
  • 1
  • 12
  • 24