SQL: How to combine count results from multiple tables into multiple columns

Question

I have two tables that are the same in their structure, but all I really want out of them is a distinct count from a single row from each into a multi-column result set.

I keep getting syntax errors, but so far haven't been able to get something that delivers the data I want, and makes it through the parser. I'm trying to figure out if this is a SQL problem (possible given that I'm using a website's implementation, rather than native MySQl/SQL/Oracle) or a me problem (much more likely).

So what I want is two unrelated (and un-primary-keyed) tables to return a COUNT(DISTINCT column) into a single result. I have tried a couple of different approaches:

select 1,2 FROM
    (SELECT COUNT(DISTINCT col1) as 1 from table1),
    (SELECT COUNT(DISTINCT col2) as 2 from table2)

SELECT *
FROM (
    SELECT COUNT(DISTINCT col1) AS 1
    FROM table1 
    )
CROSS JOIN (
    SELECT COUNT(DISTINCT col2) AS 2
    FROM table2
    )

I have also messed around with 4-5 different uses of union || union all to no avail. Curious what thoughts someone more schooled in the arts of SQL might have. Thanks.

Name your columns with words, not number, though don't recall if a number is not allowed as an alias. Try ... as col1... and ... as col2 ... — Jorge Campos
– Jorge Campos, Commented Apr 8, 2017 at 21:48

Jorge Campos · Accepted Answer · 2017-04-08 22:08:22Z

4

Depending on the database RDBM you are, a few things could change in syntax. In the ANSI Sql definition your query should be:

select col1, col2 FROM
    (SELECT COUNT(DISTINCT col1) as col1 from table1) as tab1,
    (SELECT COUNT(DISTINCT col2) as col22 from table2) as tab2

You have to add alias for all sub queries. Also name your columns with words, not number, it is easier to understand. Though I don't recall if a number is not allowed as an alias in SQL ANSI.

Without aliases for the subqueries you can use like this:

-- For MySql, PostgreSql, SQL Server (not sure though)
select (SELECT COUNT(DISTINCT col1)
          from table1) as col1,
       (SELECT COUNT(DISTINCT col2) as col22 
          from table2) as col2

-- For Oracle
select (SELECT COUNT(DISTINCT col1)
          from table1) as col1,
       (SELECT COUNT(DISTINCT col2) as col22 
          from table2) as col2
  from dual

-- For DB2
select (SELECT COUNT(DISTINCT col1)
          from table1) as col1,
       (SELECT COUNT(DISTINCT col2) as col22 
          from table2) as col2
  from sysibm.sysdummy1

Side note: you can use a number as an alias if you surround it with double quotes " (this is SQL ANSI and will work everywhere) like this:

select "1", "2" FROM
    (SELECT COUNT(DISTINCT col1) as "1" from table1) a, --don't forget the table alias
    (SELECT COUNT(DISTINCT col2) as "2" from table2) b

Mysql Also allows you to use back ticks:

select `1`, `2` FROM
    (SELECT COUNT(DISTINCT col1) as `1` from table1) a,
    (SELECT COUNT(DISTINCT col2) as `2` from table2) b

edited Apr 8, 2017 at 22:08

answered Apr 8, 2017 at 21:50

Jorge Campos

23.8k7 gold badges65 silver badges95 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

devio Over a year ago

I was confused by the double aliases in the first solution, but it works in MSSQL

Jorge Campos Over a year ago

@devio That is just a renaming of a column been specified in the COUNT function. When you use a count function the database (all of then) doesn't get the same name (normally it uses the count function as name), so you name it as the column name you are counting (of course if you prefer to do so.)

scuttlemonkey Over a year ago

This rundown was perfect for what I needed, when I moved to a real platform. Digging into the web implementation (just got access to code) it looks like sub queries aren't supported. Thanks for taking the time!

Collectives™ on Stack Overflow

SQL: How to combine count results from multiple tables into multiple columns

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related