Multiple Queries in different table

(Also posted here.)

So I have two tables, one is invalid table and the other is valid table.

valid table:

id
status
date

invalid table:

id
status
date

I have to produce a report with this output:

date       on-time  late  total  valid  invalid1  invalid2  total  rate
---------  -------  ----  -----  -----  --------  --------  -----  ----
9/10/2011  4        10    14     3      3         3         6   

• date: common fields on the 2 tables, field to group by, how many records on that day has
• on-time: count of all the id on the valid table
• late: count of all the records(id) on the invalid table
• total: total of on-time and late
• valid: count of id on the valid table with the "valid" status
• invalid1: count of id on the invalid table with "invalid1" status
• invalid2: count of id on the invalid table with "invalid2" status
• total: total of valid, invalid1, invalid2
• rate: average of totals

It's basically multiple queries with different table. 开发者_如何转开发How can I achieve it?

Someting like this?

SELECT
    *,
    (result.total + result._total) / 2 AS rate
FROM (
    SELECT
        date,
        SUM(CASE WHEN data.valid = 1 THEN 1 ELSE 0 END) AS ontime,
        SUM(CASE WHEN data.valid = 0 THEN 1 ELSE 0 END) AS late,
        COUNT(*) AS total,
        SUM(CASE WHEN data.valid = 1 AND data.status = 'valid' THEN 1 ELSE 0 END) AS valid,
        SUM(CASE WHEN data.valid = 0 AND data.status = 'invalid1' THEN 1 ELSE 0 END) AS invalid1,
        SUM(CASE WHEN data.valid = 0 AND data.status = 'invalid2' THEN 1 ELSE 0 END) AS invalid2,
        SUM(CASE WHEN data.status IN ('valid', 'invalid', 'invalid2') THEN 1 ELSE 0 END) AS _total

    FROM (
        SELECT
            date,
            status,
            valid = 1
        FROM
            Valid
        UNION ALL
        SELECT
            date,
            status,
            valid = 0
        FROM
            InValid ) AS data
    GROUP BY
        date) AS result

SELECT date, ontime, late, ontime+late total, valid, invalid1, invalid2, valid+invalid1+invalid2 total
FROM
(SELECT date,
       COUNT(*) late,
       COUNT(IIF(status = 'invalid1', 1, NULL)) invalid1,
       COUNT(IIF(status = 'invalid2', 1, NULL)) invalid2,
FROM invalid
GROUP BY date
) JOIN (
SELECT date,
       COUNT(*) ontime,
       COUNT(IIF(status = 'valud', 1, NULL)) valid,
FROM valid
GROUP BY date
) USING (date)

First of all, it seems that you are holding exactly the same information in 2 tables - I would recommend merging those tables together and add an additional boolean column called valid to hold the info related to validity of the record.

The query on your existent DB structure might look something like this:

SELECT unioned.* FROM (
   ( SELECT v.date AS date, v.status AS status, v.id AS id, COUNT(id) AS valid, 0 AS invalid1, 0 AS invalid2 FROM valid v GROUP BY v.date)
    UNION
   ( SELECT i1.date AS date, i1.status AS status, i1.id AS id, 0 AS valid, COUNT(i1.id) AS invalid1, 0 AS invalid2 FROM invalid1 i1 GROUP BY i1.date)
   UNION
   ( SELECT i2.date AS date, i2.status AS status, i2.id AS id, 0 AS valid, 0 AS invalid1, COUNT(i.id) AS invalid2 FROM invalid1 i1 GROUP BY i1.date)

) AS unioned GROUP BY unioned.date