JackTurky JackTurky - 3 years ago 164
SQL Question

How to get column ordered by average of a field from some table in SQL

I think that is not easy to understand what I need reading title, so I'll explain better.
My database is like:

Table1Table1Table2Table2Table3Table3 and other tables like these...

Users will insert into textfield a value like

1
and I will show them only the name that contains
1
. I do this in this way:

ArrayList<String> found = new ArrayList<String>();
ArrayList<String> allTable = getAllTableInDB();

for(String table:allTable){
try {
ResultSet rs = stat.executeQuery("SELECT * FROM '"+table+"'");
while(rs.next()){
if(!found.contains(rs.getString("name")) && rs.getString("name").equals(WhatUserInserts))
found.add(rs.getString("name"));
}
} catch (SQLException ex) {
ex.printStackTrace();
Logger.getLogger(DB.class.getName()).log(Level.SEVERE, null, ex);
}
}


What I want to do now is to calculate the "best" of this name found ordering by average of column v.

For one table I know that I can use something like:

SELECT name FROM table GROUP BY name ORDER BY AVG(v) DESC


How can I do the same on this limit of value of some table?

I hope I explain my question well

SOLVED in this way THANKS TO
Dan P
:

ArrayList<String> tables = getAllTableInDB();
String query = "SELECT * FROM (";
String union = " UNION ";

for(int i=0;i<tables.size();i++){
query+="SELECT * FROM '"+tables.get(i)+"' WHERE name LIKE '%"+find+"%'";
query = i==tables.size()-1?query:query+union;
}

query+=") GROUP BY nome ORDER BY AVG(venduti) DESC";

Answer Source

Assuming you want the Max or Avg of all tables this will get you the max V value for name. If you want Avgerage use AVG. I didn't exactly understand the question but I think this what you want.

SELECT Name, Max(V)
FROM
(
  SELECT Name, v
  FROM TABLE1
  UNION
  SELECT Name, v
  FROM TABLE2
  UNION
  SELECT Name, v
  FROM TABLE3
) AS AllTables
GROUP BY Name
WHERE Name = 'Jon Doe'

After commenting you asked if you could enhance performance. Yes by including the name in the where clause for each table assuming there is an index / primary key on the name. You should probably used a table valued function if you go this route.

DECLARE @Name NVARCHAR(50)
SET @Name = 'Jon Doe' 

SELECT Name, Max(V)
FROM
(
  SELECT Name, v
  FROM TABLE1
  WHERE Name = @Name
  UNION
  SELECT Name, v
  FROM TABLE2
  WHERE Name = @Name
  UNION
  SELECT Name, v
  FROM TABLE3
  WHERE Name = @Name
) AS AllTables
GROUP BY Name
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download