MySQL - join query results vertically
I am trying to create a summary report for to capture daily stats. Basically I need the outcome similar to:
Table_Name Updated_Rows Created_Rows Date Table 1 10 5 2019-04-23 Table 2 17 55 2019-04-23
Now I can fetch the individual values using basic commands:
select count(*) as created_rows from accounts where date(updated_at) = date(now())
select count(*) as created_rows from accounts where date(created_at) = date(now())
Can also combine the data using the UNINON ALL,
(SELECT table_name FROM information_schema.tables where table_name='accounts') UNION ALL (select count(*) as created_rows from accounts where date(created_at) = date(now())) UNION ALL (select count(*) as updated_rows from accounts where date(updated_at) = date(now()))
However the output I get is kind of stacked vertically and I wish to retain the labels/column names and would want to add data row by row for all the tables I want to assess.
I am sure there is an easier way but I can't seem the find a way out to get this done. Don't need the final query, just help me with a direction to look towards.
For a single table, you can do the following:
SELECT 'account' AS 'Table_Name' , SUM(date(updated_at) = date(now())) 'Updated_Rows' , SUM(date(created_at) = date(now())) 'Created_Rows' , date(now()) AS 'Date' FROM accounts
SUM(date(updated_at) = date(now())) is basically the same as
IF(SUM(date(updated_at) = date(now())), 1, 0)
Then UNION ALL result from other tables with the similar query.
MySQL UNION: Combining Results of Two or More Queries, To combine result set of two or more queries using the UNION operator, these A JOIN combines result sets horizontally, a UNION appends result set vertically. Many times, you have a small terminal screen and you are in a mysql prompt where paginating is hard to do and your query outputs are hard to read. You can output query results vertically instead of a table format. Just type \G instead of a ; when you end you queries in mysql prompt.
You could try something like the following (untested):
select '@x' as table_name, (select count(*) FROM @x and date(created_at) = date(now())) as created_rows, (select count(*) FROM @x and date(updated_at) = date(now())) as updated_rows
As part of a prepared statement (https://dev.mysql.com/doc/refman/5.6/en/sql-syntax-prepared-statements.html) or proc (https://firstname.lastname@example.org/mysql-stored-procedures-101-6b4fe230967) or just union to get multiple tables (note that you'd have to change some syntax). I'm not sure how you need to run this, so I'm not sure what exactly you need, what kind of performance you're after, if you're just going to make a script and manually select the tables or if you're trying to run this on all tables, etc.
EDIT: jxc's query is much better than mine!
MySQL: Filter results by joining a vertical table, You might find this answer a little adventurous, so here it goes First some sample data. DROP DATABASE IF EXISTS beercan; CREATE� As you can see, the duplicates appear in the combined result set because of the UNION ALL operation. UNION vs. JOIN. A JOIN combines result sets horizontally, a UNION appends result set vertically. The following picture illustrates the difference between UNION and JOIN:
Is this what you are looking for? If so, make sure to change the table names in the subsequent
UNION ALL queries.
SELECT 'accounts' AS TableName, SUM(DATE(updated_at) = DATE(NOW())) AS updated_rows, SUM(DATE(created_at) = DATE(NOW())) AS created_rows, DATE(NOW()) AS `Date` FROM accounts UNION ALL SELECT 'accounts2' AS TableName, SUM(DATE(updated_at) = DATE(NOW())) AS updated_rows, SUM(DATE(created_at) = DATE(NOW())) AS created_rows, DATE(NOW()) AS `Date` FROM accounts2 and so forth....
EDIT This query is identical to jxc's query posted earlier
How to join two table and show one query result in MySQL , Here is the query you need: SELECT name FROM ( SELECT name FROM table1 UNION SELECT name FROM table2 ) A;. Here is some sample code based on� Works best for queries returning 10 or less records. It displays the DATALENGTH for text and image fields; Here is a brief description (taken from the post): This procedure will allow you to display results vertically (down) instead of across the screen. It's great when you are dealing with a query that has 50 columns and just a couple of records
SQL Tutorial - Union, A JOIN statement adds additional table columns to a result set (horizontally), UNION combines row results from one table with rows of another table (vertically) . Displaying Query Results Vertically. Some query results are much more readable when displayed vertically, instead of in the usual horizontal table format. Queries can be displayed vertically by terminating the query with \G instead of a semicolon.
MySQL Tutorial - MySQL By Examples for Beginners, We use \G (instead of ';' ) to display the results vertically. and not recoverable! mysql> DROP DATABASE IF EXISTS southwind; Query OK, 1 rows affected You can also concatenate a few columns as one (e.g., joining the last name and first� Summary: in this tutorial, you will learn various MySQL join clauses in the SELECT statement to query data from two tables. Introduction to MySQL join clauses. A relational database consists of multiple related tables linking together using common columns which are known as foreign key columns. Because of this, data in each table is incomplete
Different Types of SQL JOINs. Here are the different types of the JOINs in SQL: (INNER) JOIN: Returns records that have matching values in both tables; LEFT (OUTER) JOIN: Returns all records from the left table, and the matched records from the right table