MySQL complex query - SUM over several databases using UNION ALL and internal JOINs

问题: I want to calculate a SUM over several databases using UNION ALL and internal JOINs. The MySQL user has the permissions to access all databases relevant. Here is my SQL q...

问题:

I want to calculate a SUM over several databases using UNION ALL and internal JOINs. The MySQL user has the permissions to access all databases relevant.

Here is my SQL query code so far:

SELECT
    SUM(summen.OP1OPVerfahren = "0") AS "Keine Operation durchgeführt",
    SUM(summen.OP1OPVerfahren = "1") AS "Bioenterics Intragastric Ballon (BIB)",
    SUM(summen.OP1OPVerfahren = "2") AS "Gastric Banding",
    SUM(summen.OP1OPVerfahren = "3") AS "Roux-en-Y Gastric Bypass",
    SUM(summen.OP1OPVerfahren LIKE "%") AS "Summe"

FROM
(
    SELECT
        SUM(op.OP1OPVerfahren = "0") AS "Keine Operation durchgeführt",
        SUM(op.OP1OPVerfahren = "1") AS "Bioenterics Intragastric Ballon (BIB)",
        SUM(op.OP1OPVerfahren = "2") AS "Gastric Banding",
        SUM(op.OP1OPVerfahren = "3") AS "Roux-en-Y Gastric Bypass",
        SUM(op.OP1OPVerfahren LIKE "%") AS "Summe"
        FROM ods01.dat_patient p
        LEFT OUTER JOIN ods01.dat_optherapie op ON op.PatID = p.ID
        WHERE Testzwecke = 0
        AND p.ID = op.PatID  -- possibly redundant
        AND NOT EXISTS (SELECT 1
                        FROM ods01.dat_optherapie op2
                        WHERE op2.PatID = p.ID AND op2.revision > op.revision)
        GROUP BY OP1OPVerfahren

    UNION ALL

    SELECT
        SUM(op.OP1OPVerfahren = "0") AS "Keine Operation durchgeführt",
        SUM(op.OP1OPVerfahren = "1") AS "Bioenterics Intragastric Ballon (BIB)",
        SUM(op.OP1OPVerfahren = "2") AS "Gastric Banding",
        SUM(op.OP1OPVerfahren = "3") AS "Roux-en-Y Gastric Bypass",
        SUM(op.OP1OPVerfahren LIKE "%") AS "Summe"
        FROM ods02.dat_patient p
        LEFT OUTER JOIN ods02.dat_optherapie op ON op.PatID = p.ID
        WHERE Testzwecke = 0
        AND p.ID = op.PatID  -- possibly redundant
        AND NOT EXISTS (SELECT 1
                        FROM ods02.dat_optherapie op2
                        WHERE op2.PatID = p.ID AND op2.revision > op.revision)
        GROUP BY OP1OPVerfahren
) summen

GROUP BY OP1OPVerfahren

Whatever I do in the first 5 lines - leave the "summen.", leave away the "summen." or exchange it with "op." - I receive an error message:

SQL error (1054): Unknown column 'summen.OP1OPVerfahren' in 'field list'

... or ...

SQL error (1054): Unknown column 'OP1OPVerfahren' in 'field list'

... or ...

SQL error (1054): Unknown column 'op.OP1OPVerfahren' in 'field list'

Where is my logic mistake?

I have seen all other references about this here, but did not find any topic conerning the integration of JOINed tables (which should not be the issue).

Has anyone got an idea what I need to change?


回答1:

You have already assign the alias so you can refer to the inner column name

SELECT
  SUM(summen.`Keine Operation durchgeführt`),
  SUM(summen.`Bioenterics Intragastric Ballon (BIB)`),
  SUM(summen.`Gastric Banding`),
  SUM(summen.`Roux-en-Y Gastric Bypass`),
  SUM(summen.`Summe`)

FROM
(
  SELECT
      SUM(op.OP1OPVerfahren = "0") AS "Keine Operation durchgeführt",
      SUM(op.OP1OPVerfahren = "1") AS "Bioenterics Intragastric Ballon (BIB)",
      SUM(op.OP1OPVerfahren = "2") AS "Gastric Banding",
      SUM(op.OP1OPVerfahren = "3") AS "Roux-en-Y Gastric Bypass",
      SUM(op.OP1OPVerfahren LIKE "%") AS "Summe"
      FROM ods01.dat_patient p
      LEFT OUTER JOIN ods01.dat_optherapie op ON op.PatID = p.ID
      WHERE Testzwecke = 0
      AND p.ID = op.PatID  -- possibly redundant
      AND NOT EXISTS (SELECT 1
                      FROM ods01.dat_optherapie op2
                      WHERE op2.PatID = p.ID AND op2.revision > op.revision)
      GROUP BY OP1OPVerfahren

  UNION ALL

  SELECT
      SUM(op.OP1OPVerfahren = "0"),
      SUM(op.OP1OPVerfahren = "1"),
      SUM(op.OP1OPVerfahren = "2"),
      SUM(op.OP1OPVerfahren = "3"),
      SUM(op.OP1OPVerfahren LIKE "%")
      FROM ods02.dat_patient p
      LEFT OUTER JOIN ods02.dat_optherapie op ON op.PatID = p.ID
      WHERE Testzwecke = 0
      AND p.ID = op.PatID  -- possibly redundant
      AND NOT EXISTS (SELECT 1
                      FROM ods02.dat_optherapie op2
                      WHERE op2.PatID = p.ID AND op2.revision > op.revision)
      GROUP BY OP1OPVerfahren
  ) summen

回答2:

You need to add OP1OPVerfahren in selection list for both inner queries-

SELECT
    SUM(OP1OPVerfahren = "0") AS "Keine Operation durchgeführt",
    SUM(OP1OPVerfahren = "1") AS "Bioenterics Intragastric Ballon (BIB)",
    SUM(OP1OPVerfahren = "2") AS "Gastric Banding",
    SUM(OP1OPVerfahren = "3") AS "Roux-en-Y Gastric Bypass",
    SUM(OP1OPVerfahren LIKE "%") AS "Summe"

FROM
(
    SELECT OP1OPVerfahren,
        SUM(op.OP1OPVerfahren = "0") AS "Keine Operation durchgeführt",
        SUM(op.OP1OPVerfahren = "1") AS "Bioenterics Intragastric Ballon (BIB)",
        SUM(op.OP1OPVerfahren = "2") AS "Gastric Banding",
        SUM(op.OP1OPVerfahren = "3") AS "Roux-en-Y Gastric Bypass",
        SUM(op.OP1OPVerfahren LIKE "%") AS "Summe"
        FROM ods01.dat_patient p
        LEFT OUTER JOIN ods01.dat_optherapie op ON op.PatID = p.ID
        WHERE Testzwecke = 0
        AND p.ID = op.PatID  -- possibly redundant
        AND NOT EXISTS (SELECT 1
                        FROM ods01.dat_optherapie op2
                        WHERE op2.PatID = p.ID AND op2.revision > op.revision)
        GROUP BY OP1OPVerfahren

    UNION ALL

    SELECT OP1OPVerfahren,
        SUM(op.OP1OPVerfahren = "0") AS "Keine Operation durchgeführt",
        SUM(op.OP1OPVerfahren = "1") AS "Bioenterics Intragastric Ballon (BIB)",
        SUM(op.OP1OPVerfahren = "2") AS "Gastric Banding",
        SUM(op.OP1OPVerfahren = "3") AS "Roux-en-Y Gastric Bypass",
        SUM(op.OP1OPVerfahren LIKE "%") AS "Summe"
        FROM ods02.dat_patient p
        LEFT OUTER JOIN ods02.dat_optherapie op ON op.PatID = p.ID
        WHERE Testzwecke = 0
        AND p.ID = op.PatID  -- possibly redundant
        AND NOT EXISTS (SELECT 1
                        FROM ods02.dat_optherapie op2
                        WHERE op2.PatID = p.ID AND op2.revision > op.revision)
        GROUP BY OP1OPVerfahren
) summen
GROUP BY OP1OPVerfahren
  • 发表于 2019-03-02 04:15
  • 阅读 ( 176 )
  • 分类:sof

条评论

请先 登录 后评论
不写代码的码农
小编

篇文章

作家榜 »

  1. 小编 文章
返回顶部
部分文章转自于网络,若有侵权请联系我们删除