Question

I have a database where I check on the size, because clients may only use a certain amount of space, aswell as they may only have a certain number of pages , which I put in the db aswell.

I have the following sql query:

SELECT table_schema "Servers",
sum( data_length + index_length ) / 1024 / 1024 "Data Base Size in MB",
sum( data_free )/ 1024 / 1024 "Free Space in MB"
FROM information_schema.TABLES
GROUP BY table_schema ;

This returns a table with the information I need. However, I would like to add 3 coloumns that also show how much each user is using. First I tried like this:

SELECT table_schema "Servers",
sum( data_length + index_length ) / 1024 / 1024 "Data Base Size in MB",
sum( data_free )/ 1024 / 1024 "Free Space in MB",
property_value "MaxWebhotelSize"
FROM information_schema.TABLES, properties
WHERE property_key LIKE  MaxWEbhotelSize 
GROUP BY table_schema
UNION
SELECT property_value "MaxPages"
FROM properties
WHERE property_key LIKE  MaxPages ;

This should take add two of the coloumns that I need. If I leave out everything from union and down it works well and adds information about maximum webhotel size to the table, but when I try to add another coloumn I get an error saying "The used SELECT statements have a different number of columns". I tried some other ways around it, but can t seem to figure out a good way where I get the right results. I m quite new to SQL, sorry if this is a stupid question.

Answer 1

In a union query, the number of columns should be equal.

So, if you have 3 columns in the first section of your union query, you should also have 3 columns in the second.

In your example, you only have 1 column in the second section. Please specify what you require in the additional 2 columns, and we might be able to assist with the query.

友情链接