Tag Info

New answers tagged

1

In addition to what @jkavalik's solution: SELECT count(col) AS totalCount , count(distinct col) AS uniqueCount FROM ... You can use case: SELECT count(col) AS totalCount , count(distinct col) AS uniqueCount , count( case when ... then 1 end ) as conditional_count FROM ... Not sure this is what you are after but you mention a where ...


1

Each query in UNION ALL is executed separately, union only "merges" the result sets so if each query needs to read entire table, it will be done twice. You can check that with EXPLAIN in MySQL or similar commands in other RDBMS. One usage for UNION (ALL) is actually optimizing queries with OR conditions, where separate queries can use indexes better than ...


1

A semi-solution: They will be in column. (But they are also in a single row, sort of.) SELECT CONCAT_WS("\n", her_name, his_name, other_name) AS name FROM foo For example: mysql> SELECT CONCAT_WS("\n", province) FROM Provinces; +---------------------------+ | CONCAT_WS("\n", province) | +---------------------------+ | Alberta | | ...


5

It is unclear to me what is a "more elegant way". Oracle you can use the following statement to make columns to rows select all_name from foo unpivot (all_name for col_name in ( his_name, her_name, other_name)); This is the syntax diagram of the select statement SELECT [ALL | DISTINCT | DISTINCTROW ] [HIGH_PRIORITY] ...


3

USING UNION Others have submitted answers trying aggregation to collect data without using UNION May 12, 2014 : Query improvements without UNION May 05, 2015 : Calculating values from three related tables, without using join or union Feb 20, 2012 : SQL Data aggregation In this instance, what makes UNION an absolute must is the merging of three columns ...


-4

Easy solution would be this: SELECT CONVERT(VARCHAR,her_name) AS name FROM foo UNION ALL SELECT CONVERT(VARCHAR,his_name) AS name FROM foo UNION ALL SELECT CONVERT(VARCHAR,other_name) AS name FROM foo


0

So it turns out that confluence uses a different structure to manage this data and now the discrepancies appear to be resolved. Here is the query: select User_Name, First_Name, Last_Name, max(Last_Login) as Last_Login FROM ( SELECT u.user_name as User_Name, u.first_name as First_Name, u.last_name Last_Name, ...


2

Memory related settings You've already addressed the key bottleneck for read heavy applications, that is, having sufficient RAM for caching. Just make sure you've set appropriately high values for shared_bufferes, work_mem, maintenance_work_mem, and effective_cache_size within your postgresql.conf file. Actually, there's a litany of good info in this ...



Top 50 recent answers are included