QuestionHow do I go about selecting COUNT(*)s from multiple tables in MySQL?Such as:SELECT COUNT(*) AS table1Count FROM table1 WHERE someCondition JOIN?? SELECT COUNT(*) AS table2Count FROM table2 WHERE someCondition CROSS JOIN? subqueries? SELECT COUNT(*) AS table3Count FROM table3 WHERE someConditionEdit:The goal is to return this:+-------------+-------------+-------------+ | table1Count | table2Count | table3Count | +-------------+-------------+-------------+ | 14 | 27 | 0 | +-------------+-------------+-------------+Answer 1You can do it by using subqueries, one subquery for each tableCount :SELECT (SELECT COUNT(*) FROM table1 WHERE someCondition) as table1Count, (SELECT COUNT(*) FROM table2 WHERE someCondition) as table2Count, (SELECT COUNT(*) FROM table3 WHERE someCondition) as table3CountAnswer 2You can do this with subqueries, e.g.:select (SELECT COUNT(*) FROM table1 WHERE someCondition) as table1Count, (SELECT COUNT(*) FROM table2 WHERE someCondition) as table2Count Answer 3You can use UNION SELECT COUNT(*) FROM table1 WHERE someCondition UNION SELECT COUNT(*) FROM table2 WHERE someCondition UNION SELECT COUNT(*) FROM table3 WHERE someConditionThe content is from StackOverflow which is translated and used in accordance with the CCBY-SA 4.0 license agreement. Original link: COUNT(*) from multiple tables in MySQL

QuestionThe query belows return 4 rows and it's good. But i need also to sum the results of all the counts in the same query. How to achieve it? I've tried different things but i get sintax errors.SELECT COUNT(*) FROM `schema1`.`table` WHERE STATE = 17 AND LEVEL = 1 UNION ALL SELECT COUNT(*) FROM `schema2`.`table` WHERE STATE = 17 AND LEVEL = 1 UNION ALL SELECT COUNT(*) FROM `schema3`.`table` WHERE STATE = 17 AND LEVEL = 1 UNION ALL SELECT COUNT(*) FROM `schema4`.`table` WHERE STATE = 17 AND LEVEL = 1Answers1. Fewer keystrokes:SELECT s1, s2, s3, s4, s1 + s2 + s3 + s4 AS total FROM ( SELECT ( SELECT COUNT(*) FROM `schema1`.`table` WHERE STATE = 17 AND LEVEL = 1 ) AS s1, ( SELECT COUNT(*) FROM `schema2`.`table` WHERE STATE = 17 AND LEVEL = 1 ) AS s2, ( SELECT COUNT(*) FROM `schema3`.`table` WHERE STATE = 17 AND LEVEL = 1 ) AS s3, ( SELECT COUNT(*) FROM `schema4`.`table` WHERE STATE = 17 AND LEVEL = 1 ) AS s4 ) AS counts;If there is a performance question, be sure to have INDEX(state, level) on each table. (The order of the columns in the index does not matter in this case.)If this is just one of many clumsy queries, you may want to reconsider having multiple databases (schemas) for the data.2. Combine your sub-queries and put a SELECT clause on it.SELECT (SELECT COUNT(*) AS cnt FROM `schema1`.`table` WHERE STATE = 17 AND LEVEL = 1) s1 , (SELECT COUNT(*) AS cnt FROM `schema2`.`table` WHERE STATE = 17 AND LEVEL = 1) s2 , (SELECT COUNT(*) AS cnt FROM `schema3`.`table` WHERE STATE = 17 AND LEVEL = 1) s3 , (SELECT COUNT(*) AS cnt FROM `schema4`.`table` WHERE STATE = 17 AND LEVEL = 1) s4 That should give you single row with 4 columns in it.If you need sum of them in single query then use below query.You can put them into a select clause and it should give you sum of it. here is an example.SELECT SUM(cnt) FROM ( SELECT COUNT(*) AS cnt FROM `schema1`.`table` WHERE STATE = 17 AND LEVEL = 1 UNION ALL SELECT COUNT(*) AS cnt FROM `schema2`.`table` WHERE STATE = 17 AND LEVEL = 1 UNION ALL SELECT COUNT(*) AS cnt FROM `schema3`.`table` WHERE STATE = 17 AND LEVEL = 1 UNION ALL SELECT COUNT(*) AS cnt FROM `schema4`.`table` WHERE STATE = 17 AND LEVEL = 1 ) tmp内容来源于Database Administrators Stack Exchange, 遵循 CCBY-SA 4.0 许可协议进行翻译与使用。原文链接： Obtain the sum of count of multiple schema and tables in one query in mysql

I am struggling to get the syntax correct below. I've tried various unions and joins without success.I need to SUM the 3 totals as 'totcount'. The data table is very large and was hoping to find a better way to obtain totcount than a 4th subquery. SELECT location.*,data.status, (SELECT COUNT(data.id) FROM data WHERE data.locid=location.locid AND data.status='NEW') AS newcount, (SELECT COUNT(data.id) FROM data WHERE data.locid=location.locid AND data.status='IN-PROGRESS') AS ipcount, (SELECT COUNT(data.id) FROM data WHERE data.locid=location.locid AND data.status='COMPLATED') AS compcount FROM TP_locations LEFT JOIN data ON data.locid=location.locid AND data.status IN('NEW','IN-PROGRESS','COMPLETED') WHERE data.status IS NOT NULL GROUP BY location.locid ORDER BY totcountAnswerYour query (if I understand the intent) can be simplified to:SELECT location.*, data.status, --this is meaningless, it will give you a random one of the 3 possible values COUNT(IF(data.status='NEW',1,null)) AS newcount, COUNT(IF(data.status='IN-PROGRESS',1,null)) AS ipcount, COUNT(IF(data.status='COMPLATED',1,null)) AS compcount, COUNT(1) AS totcount FROM TP_locations JOIN data ON data.locid=location.locid AND data.status IN('NEW','IN-PROGRESS','COMPLETED') GROUP BY location.locid ORDER BY totcountThen you can order by whichever column.The content is from StackOverflow which is translated and used in accordance with the CCBY-SA 4.0 license agreement. Original link: ORDER BY with Multiple Counts and SUM()

QuestionI have seen how to do multiple counts for different tables, but never how to combine them all into one. I have a MySQL DB where I am running the following query:SELECT characters.name, COUNT(*) AS wiki_unlocks FROM wiki_items INNER JOIN characters ON characters.character_id=wiki_items.character_id GROUP BY wiki_items.character_id ORDER BY wiki_unlocks DESC LIMIT 10;This is giving me the following which is great:name wiki_unlocks player1 2 player2 1I want to get a combined count of all of the 'wiki_xxxx' tables. For example I want 'wiki_items'(above) + 'wiki_armors' + 'wiki_weapons' + ...Thanks for any help :D3 Answers1. If performance can be a problem, because tables have lots of rows, I would do this way. Grouping and counting first and joining tables next.SELECT characters.name, COALESCE(count_unlocks,0) AS unlocks, COALESCE(count_armors,0) AS armors, COALESCE(count_weapons,0) AS weapons, COALESCE(count_unlocks,0) + COALESCE(count_armors,0) + COALESCE(count_weapons,0) AS total FROM characters LEFT JOIN (SELECT wiki_items.character_id, count(*) AS count_unlocks from wiki_items GROUP BY wiki_items.character_id) AS wiki_unlocks ON characters.character_id = wiki_unlocks.character_id LEFT JOIN (SELECT wiki_armors.character_id, count(*) AS count_armors from wiki_armors GROUP BY wiki_armors.character_id) AS wiki_armors ON characters.character_id = wiki_armors.character_id LEFT JOIN (SELECT wiki_weapons.character_id, count(*) AS count_weapons from wiki_weapons GROUP BY wiki_weapons.character_id) AS wiki_weapons ON characters.character_id = wiki_weapons.character_id2. It's probably easiest to do each count as a subselect:SELECT c.name , (select COUNT(i.character_id) From wiki_items i Where c.character_id=i.character_id ) as wiki_unlocks , (select COUNT(a.character_id) From wiki_armors a Where c.character_id=a.character_id ) as wiki_armors , (select COUNT(w.character_id) From wiki_weapons w Where c.character_id=w.character_id ) as wiki_weapons FROM characters c3. Maybe it helps:SELECT Sum( a.count ) FROM( SELECT Count( * ) AS count FROM Table1 UNION ALL SELECT Count( * ) AS count FROM Table2 UNION ALL SELECT Count( * ) AS count FROM Table3 UNION ALL SELECT Count( * ) AS count FROM Table4 ) a内容来源于Database Administrators Stack Exchange, 遵循 CCBY-SA 4.0 许可协议进行翻译与使用。原文链接：Count multiple tables as one count

In database terms, a  (pronounced “skee-muh” or “skee-mah”) is the organisation and structure of a database. Both schemas and schemata can be used as plural forms.A schema contains schema objects, which could be tables, columns, data types, views, stored procedures, relationships, primary keys, foreign keys, etc.A database schema can be represented in a visual diagram, which shows the database objects and their relationship with each other.A basic schema diagram representing a small three-table database.Above is a simple example of a schema diagram. It shows three tables, along with their data types, relationships between the tables, as well as their primary keys and foreign keys.Here is a more complex example of a database schema:A database schema diagram of the Sakila Sample Database.In this case, the schema diagram has been separated into four sections:Customer Data: Data related to the customers, such as their name, address, etcBusiness: Data required to run the business, such as staff, store locations, payment details, etcInventory: Details on all products. In this case the products are movies, so it contains data such as movie title, its category, the actors, etc.Views: Special view on data used for appraisals.So by looking at these schema diagrams, we could go ahead and create a database. In fact, MySQL Workbench allows you to generate a CREATE TABLE script straight from the diagram. You can then use the script to create a database. You can even reverse engineer a database into a diagram.Is a Schema and a Database the Same Thing?There’s a lot of confusion about schemas when it comes to databases. The question often arises whether there’s a difference between schemas and databases and if so, what is the difference.Depends on the VendorPart of the reason for the confusion is that database systems tend to approach schemas in their own way.The MySQL documentation states that physically, . Therefore, a schema and a database are the same thing.However, the Oracle Database documentation states that certain objects can be stored inside a database but not inside a schema. Therefore, a schema and a database are two different things.And according to this SQL Server technical article, a schema is a separate entity inside the database. So, they are two different things.So, depending on the RDBMS you use, schemas and databases may or may not be the same thing.What about the SQL Standard?The ISO/IEC 9075-1 SQL standard defines a schema as .If you were confused before, hope I haven’t just made it worse…Broad MeaningAnother reason for the confusion is probably due to the fact that the term schema has such a broad meaning. It has different connotations within different contexts.The word schema originates from the Greek word skhēma, which means form, figure, shape, or plan.Schema is used in psychology to describe an organised pattern of thought or behaviour that organises categories of information and the relationships among them.Before designing a database, we also need to look at the categories of information and the relationships among them. We need to create a conceptual schema before we even start with the physical schema within the DBMS.In software development, when discussing schemas, one could be discussing conceptual schemas, physical schemas, internal schemas, external schemas, logical schemas, etc . Each of these has its own specific meaning.Schema Definitions by DBMSHere’s a quick definition of schema from the three leading database systems:MySQLConceptually, a schema is a set of interrelated database objects, such as tables, table columns, data types of the columns, indexes, foreign keys, and so on.….In MySQL, physically, a schema is synonymous with a database. You can substitute the keyword SCHEMA instead of DATABASE in MySQL SQL syntax, for example using CREATE SCHEMA instead of CREATE DATABASE.Source: “MySQL Glossary”. MySQL 5.7 Reference Manual. MySQL. Retrieved 6 June 2016.SQL ServerThe names of tables, fields, data types, and primary and foreign keys of a database.“Glossary”. SQL Server 2016 Technical Documentation. Microsoft Developer Network. Retrieved 6 June 2016. Oracle DatabaseOracle Database‘s schema system is quite different to the other systems. Oracle’s schema is very much tied to the database user.A schema is a collection of logical structures of data, or schema objects. A schema is owned by a database user and has the same name as that user. Each user owns a single schema.Source: “Database Objects”.  Oracle Database Online Documentation 12c Release 1 (12.1). Oracle Help Center. Retrieved 6 June 2016.This article on schema definitions by DBMS provides more detail.Creating SchemasDespite their differences in defining schemas, each of the three aforementioned DBMSs support the CREATE SCHEMA statement.And that’s where the similarity ends.MySQLIn MySQL, CREATE SCHEMA creates a database.This is because CREATE SCHEMA is a synonym for CREATE DATABASE.  In other words, you can use CREATE SCHEMA or CREATE DATABASE to do the same thing.Oracle DatabaseIn Oracle Database, the CREATE SCHEMA statement doesn’t actually create a schema. This is because a schema is already created with each database user.In Oracle, the CREATE USER  statement creates the schema.In Oracle, CREATE SCHEMA statement lets you populate your schema with tables and views and grant privileges on those objects without having to issue multiple SQL statements in multiple transactions.SQL ServerIn SQL Server, CREATE SCHEMA will create a schema by the name you give it.Unlike MySQL, the CREATE SCHEMA statement creates a schema that is defined separately to the database.Unlike Oracle, the CREATE SCHEMA statement actually creates the schema.In SQL Server, once you create the schema, you can then add users and objects to it.ConclusionThe term schema can be used within many different contexts. In the context of creating schemas within a specific database management system, you’ll need to work with however that DBMS defines schemas.And when you switch to a new DBMS, be sure to look up how that system defines schemas.