How can I select from many table to one table?

debugcn Published at Dev

Anue

I want to count data in table 8 table by date and result it as one table

______|| table 1 || table 2 || table 3 || table 4 ||
date1 || 7,000   || 8,000   || 9,000   || 10,000  ||  
date1 || 7,000   || 8,000   || 9,000   || 10,000  ||

Giannis Paraskevopoulos

If you are in SQL Server:

;With Table1CTE AS
(
    SELECT CONVERT(DATETIME,CONVERT(VARCHAR,Date1,112)) AS Date1,
           COUNT(*) AS Table1
    FROM   Table1
    GROUP BY CONVERT(DATETIME,CONVERT(VARCHAR,Date1,112))
)
,Table2CTE AS
(
    SELECT CONVERT(DATETIME,CONVERT(VARCHAR,Date1,112)) AS Date1,
           COUNT(*) AS Table2
    FROM   Table2
    GROUP BY CONVERT(DATETIME,CONVERT(VARCHAR,Date1,112))
)
,Table3CTE AS
(
    SELECT CONVERT(DATETIME,CONVERT(VARCHAR,Date1,112)) AS Date1,
           COUNT(*) AS Table3
    FROM   Table3
    GROUP BY CONVERT(DATETIME,CONVERT(VARCHAR,Date1,112))
)
,Table4CTE AS
(
    SELECT CONVERT(DATETIME,CONVERT(VARCHAR,Date1,112)) AS Date1,
           COUNT(*) AS Table4
    FROM   Table4
    GROUP BY CONVERT(DATETIME,CONVERT(VARCHAR,Date1,112))
)
SELECT Date1,
       Table1,
       Table2,
       Table3,
       Table4
FROM   Table1CTE T1
       FULL OUTER JOIN Table2CTE T2
            ON T1.date1 = T2.date1
       FULL OUTER JOIN Table3CTE T3
            ON T1.date1 = T3.date1
       FULL OUTER JOIN Table4CTE T4
            ON T1.date1 = T4.date1

I am using FULL OUTER JOIN as i do not know if all dates are present in all tables.

You could always try joining subqueries:

SELECT Date1,
       Table1,
       Table2,
       Table3,
       Table4
FROM   (
           SELECT CONVERT(DATETIME,CONVERT(VARCHAR,Date1,112)) AS Date1,
                  COUNT(*) AS Table1
           FROM   Table1
           GROUP BY CONVERT(DATETIME,CONVERT(VARCHAR,Date1,112))
       ) T1
       FULL OUTER JOIN 
       (
           SELECT CONVERT(DATETIME,CONVERT(VARCHAR,Date1,112)) AS Date1,
                  COUNT(*) AS Table2
           FROM   Table2
           GROUP BY CONVERT(DATETIME,CONVERT(VARCHAR,Date1,112))
       ) T2
            ON T1.date1 = T2.date1
       FULL OUTER JOIN 
       (
           SELECT CONVERT(DATETIME,CONVERT(VARCHAR,Date1,112)) AS Date1,
                  COUNT(*) AS Table3
           FROM   Table3
           GROUP BY CONVERT(DATETIME,CONVERT(VARCHAR,Date1,112))
       ) T3
            ON T1.date1 = T3.date1
       FULL OUTER JOIN 
       (
           SELECT CONVERT(DATETIME,CONVERT(VARCHAR,Date1,112)) AS Date1,
                  COUNT(*) AS Table4
           FROM   Table4
           GROUP BY CONVERT(DATETIME,CONVERT(VARCHAR,Date1,112))
       ) T4
            ON T1.date1 = T4.date1

Collected from the Internet

Please contact [email protected] to delete if infringement.