CODEWITHSUNDEEP
sqlodbcfirebird
user3328740
user3328740

Reputation: 33

ODBC Firebird Sql Query - Syntax

Trying to get an slightly more complex sql statement structured but can't seem to get the syntax right. Trying to select counts, of various columns, in two different tables.

SELECT 
     SUM(ColumninTable1), 
     SUM(Column2inTable1), 
     COUNT(DISTINCT(Column3inTable1)) 
FROM TABLE1

This works, however I can't for the life of me figure out how to add in a COUNT(DISTINCT(Column1inTable2) FROM TABLE2 with what syntax.

Upvotes: 1

Views: 571

Answers (2)

Mark Rotteveel
Mark Rotteveel

Reputation: 108991

There are several solutions you can take:

Disjunct FULL OUTER JOIN

SELECT 
    SUM(MYTABLE.ID) as theSum,
    COUNT(DISTINCT MYTABLE.SOMEVALUE) as theCount,
    COUNT(DISTINCT MYOTHERTABLE.SOMEOTHERVALUE) as theOtherCount
FROM MYTABLE
FULL OUTER JOIN MYOTHERTABLE ON 1=0

UNION two queries and leave the column for the other table null

SELECT 
    MAX(theSum) as theSum, 
    MAX(theCount) as theCount, 
    MAX(theOtherCount) AS theOtherCount
FROM (
    SELECT
        SUM(ID) as theSum,
        COUNT(DISTINCT SOMEVALUE) as theCount,
        NULL as theOtherCount
    FROM MYTABLE
    UNION ALL
    SELECT
        NULL,
        NULL,
        COUNT(DISTINCT SOMEOTHERVALUE)
    FROM MYOTHERTABLE
)

Query 'with a query per column' against a single record table (eg RDB$DATABASE)

SELECT
    (SELECT SUM(ID) FROM MYTABLE) as theSum,
    (SELECT COUNT(DISTINCT SOMEVALUE) FROM MYTABLE) as theCount,
    (SELECT COUNT(DISTINCT SOMEOTHERVALUE) FROM MYOTHERTABLE) as theOtherCount
FROM RDB$DATABASE

CTE per table + cross join

WITH query1 AS (
    SELECT
        SUM(ID) as theSum,
        COUNT(DISTINCT SOMEVALUE) as theCount
    FROM MYTABLE
),
query2 AS (
    SELECT
        COUNT(DISTINCT SOMEOTHERVALUE) as theOtherCount
    FROM MYOTHERTABLE
)
SELECT 
    query1.theSum,
    query1.theCount,
    query2.theOtherCount
FROM query1
CROSS JOIN query2

There are probably some more solutions. You might want to ask yourself if it is worth the effort of coming up with a (convoluted, hard to understand) single query to get this data were two queries are sufficient, easier to understand and in the case of large datasets: two separate queries might be faster.

Upvotes: 1

Edgard Leal
Edgard Leal

Reputation: 2720

In this case all "count" would return the same value.

Try to do the same using sub queries: 

  Select 
   (Select count (*) from Table1), 
   (Select count (*) from table2) 
  from Table3

Upvotes: 0

Related Questions