Subquery returns more than 1 row mysql ошибка

I got an error: #1242 — Subquery returns more than 1 row when i run this sql.

CREATE VIEW test 
AS 
  SELECT cc_name, 
         COUNT() AS total, 
         (SELECT COUNT(*) 
            FROM bed 
           WHERE respatient_id > 0 
        GROUP BY cc_name) AS occupied_beds, 
         (SELECT COUNT(*) 
            FROM bed 
           WHERE respatient_id IS NULL 
        GROUP BY cc_name) AS free_beds 
    FROM bed 
GROUP BY cc_name;

OMG Ponies

321k79 gold badges516 silver badges499 bronze badges

asked Oct 23, 2011 at 16:00

The problem is that your subselects are returning more than one value — IE:

SELECT ...
       (SELECT COUNT(*) 
          FROM bed 
         WHERE respatient_id IS NULL 
      GROUP BY cc_name) AS free_beds,
       ...

…will return a row for each cc_name, but SQL doesn’t support compacting the resultset for the subselect — hence the error.

Don’t need the subselects, this can be done using a single pass over the table using:

  SELECT b.cc_name, 
         COUNT(*) AS total, 
         SUM(CASE 
               WHEN b.respatient_id > 0 THEN 1 
               ELSE 0 
             END) AS occupied_beds, 
         SUM(CASE 
               WHEN b.respatient_id IS NULL THEN 1 
               ELSE 0 
             END) AS free_beds 
    FROM bed b
GROUP BY b.cc_name

answered Oct 23, 2011 at 16:05

OMG PoniesOMG Ponies

321k79 gold badges516 silver badges499 bronze badges

This is because your subqueries (the SELECT bits that are inside parentheses) are returning multiple rows for each outer row. The problem is with the GROUP BY; if you want to use subqueries for this, then you need to correlate them to the outer query, by specifying that they refer to the same cc_name as the outer query:

CREATE VIEW test 
AS 
  SELECT cc_name, 
         COUNT()             AS total, 
         (SELECT COUNT() 
          FROM   bed 
          WHERE  cc_name = bed_outer.cc_name
          AND    respatient_id > 0) AS occupied_beds, 
         (SELECT COUNT(*) 
          FROM   bed 
          WHERE  cc_name = bed_outer.cc_name
          WHERE  respatient_id IS NULL) AS free_beds 
  FROM   bed AS bed_outer
  GROUP  BY cc_name;

(See http://en.wikipedia.org/wiki/Correlated_subquery for information about correlated subqueries.)

But, as OMG Ponies and a1ex07 say, you don’t actually need to use subqueries for this if you don’t want to.

answered Oct 23, 2011 at 16:11

ruakhruakh

172k26 gold badges266 silver badges302 bronze badges

Your subqueries return more than 1 row. I think you you need something like :

 SELECT COUNT(*) AS total, 
 COUNT(CASE WHEN respatient_id > 0 THEN 1 END) AS occupied_beds,
 COUNT(CASE WHEN respatient_id IS NULL THEN 1 END) AS free_beds          
 FROM   bed 
 GROUP  BY cc_name

You can also try to use WITH ROLLUP + pivoting (mostly for learning purposes, it’s a much longer query ) :

SELECT cc_name, 
MAX(CASE 
 WHEN num_1 = 1 THEN tot_num END) AS free_beds,

MAX(CASE 
 WHEN num_1 = 2 THEN tot_num END) AS occupied_beds,

MAX(CASE 
 WHEN num_1 = IS NULL THEN tot_num END) AS total

FROM
(SELECT cc_name, CASE 
WHEN respatient_id > 0 THEN 1
WHEN respatient_id IS NULL THEN 2
ELSE 3 END as num_1,
COUNT(*) as tot_num
FROM  bed
WHERE 
CASE 
WHEN respatient_id > 0 THEN 1
WHEN respatient_id IS NULL THEN 2
ELSE 3 END != 3
GROUP BY cc_name,
num_1 WITH ROLLUP)A
GROUP BY cc_name

answered Oct 23, 2011 at 16:06

a1ex07a1ex07

36.6k12 gold badges87 silver badges102 bronze badges

SELECT COUNT() 
          FROM   bed 
          WHERE  respatient_id > 0 
          GROUP  BY cc_name

You need to remove the group-by in the sub query, so possibly something like

SELECT COUNT(*) 
          FROM   bed 
          WHERE  respatient_id > 0

or possibly — depending on what your application logic is….

SELECT COUNT(*) from (
          select count(*),cc_name FROM   bed 
          WHERE  respatient_id > 0 
          GROUP  BY cc_name) filterview

answered Oct 23, 2011 at 16:02

SorenSoren

14.3k4 gold badges40 silver badges67 bronze badges

Источник

I got an error: #1242 — Subquery returns more than 1 row when i run this sql.

CREATE VIEW test 
AS 
  SELECT cc_name, 
         COUNT() AS total, 
         (SELECT COUNT(*) 
            FROM bed 
           WHERE respatient_id > 0 
        GROUP BY cc_name) AS occupied_beds, 
         (SELECT COUNT(*) 
            FROM bed 
           WHERE respatient_id IS NULL 
        GROUP BY cc_name) AS free_beds 
    FROM bed 
GROUP BY cc_name;

OMG Ponies

321k79 gold badges516 silver badges499 bronze badges

asked Oct 23, 2011 at 16:00

The problem is that your subselects are returning more than one value — IE:

SELECT ...
       (SELECT COUNT(*) 
          FROM bed 
         WHERE respatient_id IS NULL 
      GROUP BY cc_name) AS free_beds,
       ...

…will return a row for each cc_name, but SQL doesn’t support compacting the resultset for the subselect — hence the error.

Don’t need the subselects, this can be done using a single pass over the table using:

  SELECT b.cc_name, 
         COUNT(*) AS total, 
         SUM(CASE 
               WHEN b.respatient_id > 0 THEN 1 
               ELSE 0 
             END) AS occupied_beds, 
         SUM(CASE 
               WHEN b.respatient_id IS NULL THEN 1 
               ELSE 0 
             END) AS free_beds 
    FROM bed b
GROUP BY b.cc_name

answered Oct 23, 2011 at 16:05

OMG PoniesOMG Ponies

321k79 gold badges516 silver badges499 bronze badges

CREATE VIEW test 
AS 
  SELECT cc_name, 
         COUNT()             AS total, 
         (SELECT COUNT() 
          FROM   bed 
          WHERE  cc_name = bed_outer.cc_name
          AND    respatient_id > 0) AS occupied_beds, 
         (SELECT COUNT(*) 
          FROM   bed 
          WHERE  cc_name = bed_outer.cc_name
          WHERE  respatient_id IS NULL) AS free_beds 
  FROM   bed AS bed_outer
  GROUP  BY cc_name;

(See http://en.wikipedia.org/wiki/Correlated_subquery for information about correlated subqueries.)

But, as OMG Ponies and a1ex07 say, you don’t actually need to use subqueries for this if you don’t want to.

answered Oct 23, 2011 at 16:11

ruakhruakh

172k26 gold badges266 silver badges302 bronze badges

Your subqueries return more than 1 row. I think you you need something like :

 SELECT COUNT(*) AS total, 
 COUNT(CASE WHEN respatient_id > 0 THEN 1 END) AS occupied_beds,
 COUNT(CASE WHEN respatient_id IS NULL THEN 1 END) AS free_beds          
 FROM   bed 
 GROUP  BY cc_name

You can also try to use WITH ROLLUP + pivoting (mostly for learning purposes, it’s a much longer query ) :

SELECT cc_name, 
MAX(CASE 
 WHEN num_1 = 1 THEN tot_num END) AS free_beds,

MAX(CASE 
 WHEN num_1 = 2 THEN tot_num END) AS occupied_beds,

MAX(CASE 
 WHEN num_1 = IS NULL THEN tot_num END) AS total

FROM
(SELECT cc_name, CASE 
WHEN respatient_id > 0 THEN 1
WHEN respatient_id IS NULL THEN 2
ELSE 3 END as num_1,
COUNT(*) as tot_num
FROM  bed
WHERE 
CASE 
WHEN respatient_id > 0 THEN 1
WHEN respatient_id IS NULL THEN 2
ELSE 3 END != 3
GROUP BY cc_name,
num_1 WITH ROLLUP)A
GROUP BY cc_name

answered Oct 23, 2011 at 16:06

a1ex07a1ex07

36.6k12 gold badges87 silver badges102 bronze badges

SELECT COUNT() 
          FROM   bed 
          WHERE  respatient_id > 0 
          GROUP  BY cc_name

You need to remove the group-by in the sub query, so possibly something like

SELECT COUNT(*) 
          FROM   bed 
          WHERE  respatient_id > 0

or possibly — depending on what your application logic is….

SELECT COUNT(*) from (
          select count(*),cc_name FROM   bed 
          WHERE  respatient_id > 0 
          GROUP  BY cc_name) filterview

answered Oct 23, 2011 at 16:02

SorenSoren

14.3k4 gold badges40 silver badges67 bronze badges

Источник

I got an error: #1242 — Subquery returns more than 1 row when i run this sql.

CREATE VIEW test 
AS 
  SELECT cc_name, 
         COUNT() AS total, 
         (SELECT COUNT(*) 
            FROM bed 
           WHERE respatient_id > 0 
        GROUP BY cc_name) AS occupied_beds, 
         (SELECT COUNT(*) 
            FROM bed 
           WHERE respatient_id IS NULL 
        GROUP BY cc_name) AS free_beds 
    FROM bed 
GROUP BY cc_name;

OMG Ponies

321k79 gold badges516 silver badges499 bronze badges

asked Oct 23, 2011 at 16:00

The problem is that your subselects are returning more than one value — IE:

SELECT ...
       (SELECT COUNT(*) 
          FROM bed 
         WHERE respatient_id IS NULL 
      GROUP BY cc_name) AS free_beds,
       ...

…will return a row for each cc_name, but SQL doesn’t support compacting the resultset for the subselect — hence the error.

Don’t need the subselects, this can be done using a single pass over the table using:

  SELECT b.cc_name, 
         COUNT(*) AS total, 
         SUM(CASE 
               WHEN b.respatient_id > 0 THEN 1 
               ELSE 0 
             END) AS occupied_beds, 
         SUM(CASE 
               WHEN b.respatient_id IS NULL THEN 1 
               ELSE 0 
             END) AS free_beds 
    FROM bed b
GROUP BY b.cc_name

answered Oct 23, 2011 at 16:05

OMG PoniesOMG Ponies

321k79 gold badges516 silver badges499 bronze badges

CREATE VIEW test 
AS 
  SELECT cc_name, 
         COUNT()             AS total, 
         (SELECT COUNT() 
          FROM   bed 
          WHERE  cc_name = bed_outer.cc_name
          AND    respatient_id > 0) AS occupied_beds, 
         (SELECT COUNT(*) 
          FROM   bed 
          WHERE  cc_name = bed_outer.cc_name
          WHERE  respatient_id IS NULL) AS free_beds 
  FROM   bed AS bed_outer
  GROUP  BY cc_name;

(See http://en.wikipedia.org/wiki/Correlated_subquery for information about correlated subqueries.)

But, as OMG Ponies and a1ex07 say, you don’t actually need to use subqueries for this if you don’t want to.

answered Oct 23, 2011 at 16:11

ruakhruakh

172k26 gold badges266 silver badges302 bronze badges

Your subqueries return more than 1 row. I think you you need something like :

 SELECT COUNT(*) AS total, 
 COUNT(CASE WHEN respatient_id > 0 THEN 1 END) AS occupied_beds,
 COUNT(CASE WHEN respatient_id IS NULL THEN 1 END) AS free_beds          
 FROM   bed 
 GROUP  BY cc_name

You can also try to use WITH ROLLUP + pivoting (mostly for learning purposes, it’s a much longer query ) :

SELECT cc_name, 
MAX(CASE 
 WHEN num_1 = 1 THEN tot_num END) AS free_beds,

MAX(CASE 
 WHEN num_1 = 2 THEN tot_num END) AS occupied_beds,

MAX(CASE 
 WHEN num_1 = IS NULL THEN tot_num END) AS total

FROM
(SELECT cc_name, CASE 
WHEN respatient_id > 0 THEN 1
WHEN respatient_id IS NULL THEN 2
ELSE 3 END as num_1,
COUNT(*) as tot_num
FROM  bed
WHERE 
CASE 
WHEN respatient_id > 0 THEN 1
WHEN respatient_id IS NULL THEN 2
ELSE 3 END != 3
GROUP BY cc_name,
num_1 WITH ROLLUP)A
GROUP BY cc_name

answered Oct 23, 2011 at 16:06

a1ex07a1ex07

36.6k12 gold badges87 silver badges102 bronze badges

SELECT COUNT() 
          FROM   bed 
          WHERE  respatient_id > 0 
          GROUP  BY cc_name

You need to remove the group-by in the sub query, so possibly something like

SELECT COUNT(*) 
          FROM   bed 
          WHERE  respatient_id > 0

or possibly — depending on what your application logic is….

SELECT COUNT(*) from (
          select count(*),cc_name FROM   bed 
          WHERE  respatient_id > 0 
          GROUP  BY cc_name) filterview

answered Oct 23, 2011 at 16:02

SorenSoren

14.3k4 gold badges40 silver badges67 bronze badges

Источник

Бывает так, что подзапрос одиночной строки возвращает более одной строки. В таком случае возникнет ошибка.

Для каждого магазина найдем одного сотрудника с должностью 'MANAGER'.

SELECT s.store_id,
       s.name,
       (SELECT e.employee_id
          FROM employee e
         WHERE e.store_id = s.store_id
           AND e.rank_id = 'MANAGER'
       ) AS employee_id
  FROM store s
 ORDER BY s.store_id

error: more than one row returned by a subquery used as an expression

Посмотрим, что там с данными не так?

SELECT e.store_id,
       e.rank_id,
       e.last_name || ' ' || e.first_name AS full_name 
  FROM employee e
 WHERE e.rank_id = 'MANAGER'
 ORDER BY e.store_id, e.last_name, e.first_name

В магазине 201 два менеджера, а в магазине 600 — три.

В такой ситуации есть два варианта развития событий. Мы можем решать неправильную задачу. Если в магазине может быть несколько менеджеров, то мы должны работать с массивом менеджеров. В таком случае может быть приемлемым получить одного из них, например, первого по алфавиту.

Возможно, менеджер в магазине должен быть только один. Просто кто-то не создал уникальный ключ, и пользователи создали несколько менеджеров в магазине. В таком случае, мы написали правильный запрос и необходимо избавляться от неправильных данных и дорабатывать структуру данных.

В любом случае, необходимо гарантировать, что в результате подзапроса будет возвращено не более одной строки. Для этого необходимо одно из:

должен быть уникальный ключ, гарантирующий, что в результате подзапроса будет не более одной строки;
использовать агрегатную функцию;
использовать LIMIT 1 для ограничения количества строк.

Воспользуемся LIMIT 1:

SELECT s.store_id,
       s.name,
       (SELECT e.employee_id
          FROM employee e
         WHERE e.store_id = s.store_id
           AND e.rank_id = 'MANAGER'
         ORDER BY e.last_name,
                  e.first_name,
                  e.middle_name
         LIMIT 1
       ) AS employee_id
  FROM store s
 ORDER BY s.store_id

P.S. Если нам нужен список ФИО, то можно воспользоваться string_agg:

SELECT s.store_id,
       s.name,
       (SELECT string_agg (
                  e.last_name || ' ' || e.first_name, '; '
                  ORDER BY e.last_name,
                           e.first_name
               )
          FROM employee e
         WHERE e.store_id = s.store_id
           AND e.rank_id = 'MANAGER'
       ) AS employees
  FROM store s
 ORDER BY s.store_id

Таких функций в PostgreSQL довольно много, и они заслуживают отдельной темы.

Источник

So I’ve just started learning MySQL with a few exercises and I’m currently stuck at these 2 problems. Since this is technically an exercise I’d really appreciate hints instead of full solutions. Here are the 2 tables first:

CREATE TABLE LINE (
INV_NUMBER int,
LINE_NUMBER int,
P_CODE varchar(10),
LINE_UNITS float(8),
LINE_PRICE float(8),
LINE_TOTAL float(8)
);
INSERT INTO LINE VALUES('1001','1','13-Q2/P2','1','14.99','14.99');
INSERT INTO LINE VALUES('1001','2','23109-HB','1','9.95','9.95');
INSERT INTO LINE VALUES('1002','1','54778-2T','2','4.99','9.98');
INSERT INTO LINE VALUES('1003','1','2238/QPD','1','38.95','38.95');
INSERT INTO LINE VALUES('1003','2','1546-QQ2','1','39.95','39.95');
INSERT INTO LINE VALUES('1003','3','13-Q2/P2','5','14.99','74.95');
INSERT INTO LINE VALUES('1004','1','54778-2T','3','4.99','14.97');
INSERT INTO LINE VALUES('1004','2','23109-HB','2','9.95','19.90');
INSERT INTO LINE VALUES('1005','1','PVC23DRT','12','5.87','70.44');
INSERT INTO LINE VALUES('1006','1','SM-18277','3','6.99','20.97');
INSERT INTO LINE VALUES('1006','2','2232/QTY','1','109.92','109.92');
INSERT INTO LINE VALUES('1006','3','23109-HB','1','9.95','9.95');
INSERT INTO LINE VALUES('1006','4','89-WRE-Q','1','256.99','256.99');
INSERT INTO LINE VALUES('1007','1','13-Q2/P2','2','14.99','29.98');
INSERT INTO LINE VALUES('1007','2','54778-2T','1','4.99','4.99');
INSERT INTO LINE VALUES('1008','1','PVC23DRT','5','5.87','29.35');
INSERT INTO LINE VALUES('1008','2','WR3/TT3','3','119.95','359.85');
INSERT INTO LINE VALUES('1008','3','23109-HB','1','9.95','9.95');

CREATE TABLE PRODUCT (
P_CODE varchar(10),
P_DESCRIPT varchar(35),
P_INDATE date,
P_QOH int,
P_MIN int,
P_PRICE float(8),
P_DISCOUNT float(8),
V_CODE int
);
INSERT INTO PRODUCT VALUES('11QER/31','Power painter, 15 psi., 3- 
nozzle','2015-11-03','8','5','109.99','0','25595');
INSERT INTO PRODUCT VALUES('13-Q2/P2','7.25-in. pwr. saw blade','2015-12- 
13','32','15','14.99','0.05',NULL);
INSERT INTO PRODUCT VALUES('14-Q1/L3','9.00-in. pwr. saw blade','2015-11- 
13','18','12','17.49','0','21344');
INSERT INTO PRODUCT VALUES('1546-QQ2','Hrd. cloth, 1/4-in., 2x50','2016-01- 
15','15','8','39.95','0','23119');
INSERT INTO PRODUCT VALUES('1558-QW1','Hrd. cloth, 1/2-in., 3x50','2016-01- 
15','23','5','43.99','0','23119');
INSERT INTO PRODUCT VALUES('2232/QTY','B&D jigsaw, 12-in. blade','2015-12- 
30','8','5','109.92','0.05','24288');
INSERT INTO PRODUCT VALUES('2232/QWE','B&D jigsaw, 8-in. blade','2015-12- 
24','6','5','99.87','0.05','24288');
INSERT INTO PRODUCT VALUES('2238/QPD','B&D cordless drill, 1/2-in.','2016- 
01-20','12','5','38.95','0.05','25595');
INSERT INTO PRODUCT VALUES('23109-HB','Claw hammer','2016-01- 
12','23','10','9.95','0.1','21225');
INSERT INTO PRODUCT VALUES('23114-AA','Sledge hammer, 12 lb.','2016-01- 
2','8','5','14.40','0.05',NULL);
INSERT INTO PRODUCT VALUES('54778-2T','Rat-tail file, 1/8-in. fine','2015- 
12-15','43','20','4.99','0','21344');
INSERT INTO PRODUCT VALUES('89-WRE-Q','Hicut chain saw, 16 in.','2016-02- 
17','11','5','256.99','0.05','24288');
INSERT INTO PRODUCT VALUES('PVC23DRT','PVC pipe, 3.5-in., 8-ft','2016-02- 
27','188','75','5.87','0','24004');
INSERT INTO PRODUCT VALUES('SM-18277','1.25-in. metal screw, ''25','2016-03- 
01','172','75','6.99','0','21225');
INSERT INTO PRODUCT VALUES('SW-23116','2.5-in. wd. screw, 50','2016-02- 
14','237','100','8.45','0','21231');
INSERT INTO PRODUCT VALUES('WR3/TT3','Steel matting, 4''x8''x1/6", .5" 
mesh','2016-01-27','18','5','119.95','0.1','25595');

PKs are line.INV_NUMBER, line.LINE_NUMBER, product.P_CODE and FKs are line.P_CODE and line.V_CODE

So the first question is to list all product sales that are greater than the average units sold for that product and add a correlated in-line sub-query to list the average units sold per product.
If I understood correctly this means that I need to sum the line.LINE_UNITS group by P_CODE then compare it to the average units sold but I keep getting the same error that says sub query returns more than 1 row. Here’s my code:

select  P_CODE,
        LINE_UNITS, 
        ( SELECT  AVG(line.LINE_UNITS) ) as ‘Unit_Average’
    from  line
    where  
        ( SELECT  sum(line.LINE_UNITS)
            from  line
            group by  P_CODE ) >
        ( SELECT  AVG(line.LINE_UNITS) );

I think I need to do a join instead and I’d really appreciate some hints.

For the second question I am supposed to list the difference between each product’s prices and the average product price. I managed to type up a few lines but I couldn’t get it to execute for all rows of the product table. I’m thinking of using over() but im having quite a lot of syntax errors. Here’s my code:

select  P_CODE, P_PRICE, 
        ( SELECT  avg(product.P_PRICE) ) as 'Average',
        if(product.P_PRICE <= 
              ( SELECT  avg(product.P_PRICE) ), 
              ( SELECT  avg(product.P_PRICE) ) - product.P_PRICE,
              product.P_PRICE - ( SELECT  avg(product.P_PRICE) )
        ) as 'Difference'
    from  product;

Thanks guys!

Источник

SET @vardt1 = '01.10.2017';
SET @vardt2 = '01.11.2017';
SET @vardt3 = '2017-10-01';
SET @vardt4 = '2017-10-30';
SET @vardt5 = '2017-11-01';
SET @vardt6 = '2017-11-30';
SET @varid1 = '34';
SET @varid2 = '35';
SET @varid3 = '36';
SET @varid4 = '71';
 
SELECT
  Deal_StfCrdRcd AS MerchantID, stfcrd.stfcrd_fio AS MerchNm, postemp.postemp_nm AS Pos
  , SUM(deal_col) AS QSold, SUM(deal_col * prod_prc) AS Itog
  , ROUND(SUM((deal_col * prod_prc) * zrp_comm)) AS Comm, zrp_okl AS OKL
  , ROUND(SUM((deal_col * prod_prc) * zrp_comm) + zrp_okl) AS Salary
  , MONTHNAME(@vardt1) AS Mnh
FROM
  deal, stfcrd, postemp, prod, zrp
WHERE
  Deal_StfCrdRcd = @varid1 AND stfcrd.StfCrd_Rcd = deal.Deal_StfCrdRcd
  AND postemp.PostEmp_Rcd = stfcrd.StfCrd_PostEmpRcd
  AND prod.Prod_Rcd = deal.Deal_ProdRcd
  AND zrp.zrpstf_rcd = deal.Deal_StfCrdRcd
  AND deal_date BETWEEN @vardt3 AND @vardt4
 
UNION
 
SELECT
  Deal_StfCrdRcd AS MerchantID, stfcrd.stfcrd_fio AS MerchNm, postemp.postemp_nm AS Pos
  , SUM(deal_col) AS QSold, SUM(deal_col * prod_prc) AS Itog
  , ROUND(SUM((deal_col * prod_prc) * zrp_comm)) AS Comm, zrp_okl AS OKL
  , ROUND(SUM((deal_col * prod_prc) * zrp_comm) + zrp_okl) AS Salary
  , MONTHNAME(@vardt1) AS Mnh
FROM
  deal, stfcrd, postemp, prod, zrp
WHERE
  Deal_StfCrdRcd = @varid2 AND stfcrd.StfCrd_Rcd = deal.Deal_StfCrdRcd
  AND postemp.PostEmp_Rcd = stfcrd.StfCrd_PostEmpRcd
  AND prod.Prod_Rcd = deal.Deal_ProdRcd
  AND zrp.zrpstf_rcd = deal.Deal_StfCrdRcd
  AND deal_date BETWEEN @vardt3 AND @vardt4
 
UNION
 
SELECT
  Deal_StfCrdRcd AS MerchantID, stfcrd.stfcrd_fio AS MerchNm, postemp.postemp_nm AS Pos
  , SUM(deal_col) AS QSold, SUM(deal_col * prod_prc) AS Itog
  , ROUND(SUM((deal_col * prod_prc) * zrp_comm)) AS Comm, zrp_okl AS OKL
  , ROUND(SUM((deal_col * prod_prc) * zrp_comm) + zrp_okl) AS Salary
  , MONTHNAME(@vardt1) AS Mnh
FROM
  deal, stfcrd, postemp, prod, zrp
WHERE
  Deal_StfCrdRcd = @varid3 AND stfcrd.StfCrd_Rcd = deal.Deal_StfCrdRcd
  AND postemp.PostEmp_Rcd = stfcrd.StfCrd_PostEmpRcd
  AND prod.Prod_Rcd = deal.Deal_ProdRcd
  AND zrp.zrpstf_rcd = deal.Deal_StfCrdRcd
  AND deal_date BETWEEN @vardt3 AND @vardt4
 
UNION
 
SELECT
  Deal_StfCrdRcd AS MerchantID, stfcrd.stfcrd_fio AS MerchNm, postemp.postemp_nm AS Pos
  , SUM(deal_col) AS QSold, SUM(deal_col * prod_prc) AS Itog
  , ROUND(SUM((deal_col * prod_prc) * zrp_comm)) AS Comm, zrp_okl AS OKL
  , ROUND(SUM((deal_col * prod_prc) * zrp_comm) + zrp_okl) AS Salary
  , MONTHNAME(@vardt1) AS Mnh
FROM
  deal, stfcrd, postemp, prod, zrp
WHERE
  Deal_StfCrdRcd = @varid4 AND stfcrd.StfCrd_Rcd = deal.Deal_StfCrdRcd
  AND postemp.PostEmp_Rcd = stfcrd.StfCrd_PostEmpRcd
  AND prod.Prod_Rcd = deal.Deal_ProdRcd
  AND zrp.zrpstf_rcd = deal.Deal_StfCrdRcd
  AND deal_date BETWEEN @vardt3 AND @vardt4
 
UNION
 
SELECT
  Deal_StfCrdRcd AS MerchantID, stfcrd.stfcrd_fio AS MerchNm, postemp.postemp_nm AS Pos
  , SUM(deal_col) AS QSold, SUM(deal_col * prod_prc) AS Itog
  , ROUND(SUM((deal_col * prod_prc) * zrp_comm)) AS Comm, zrp_okl AS OKL
  , ROUND(SUM((deal_col * prod_prc) * zrp_comm) + zrp_okl) AS Salary
  , MONTHNAME(@vardt2) AS Mnh
FROM
  deal, stfcrd, postemp, prod, zrp
WHERE
  Deal_StfCrdRcd = @varid1 AND stfcrd.StfCrd_Rcd = deal.Deal_StfCrdRcd
  AND postemp.PostEmp_Rcd = stfcrd.StfCrd_PostEmpRcd
  AND prod.Prod_Rcd = deal.Deal_ProdRcd
  AND zrp.zrpstf_rcd = deal.Deal_StfCrdRcd
  AND deal_date BETWEEN @vardt5 AND @vardt6
 
UNION
 
SELECT
  Deal_StfCrdRcd AS MerchantID, stfcrd.stfcrd_fio AS MerchNm, postemp.postemp_nm AS Pos
  , SUM(deal_col) AS QSold, SUM(deal_col * prod_prc) AS Itog
  , ROUND(SUM((deal_col * prod_prc) * zrp_comm)) AS Comm, zrp_okl AS OKL
  , ROUND(SUM((deal_col * prod_prc) * zrp_comm) + zrp_okl) AS Salary
  , MONTHNAME(@vardt2) AS Mnh
FROM
  deal, stfcrd, postemp, prod, zrp
WHERE
  Deal_StfCrdRcd = @varid2 AND stfcrd.StfCrd_Rcd = deal.Deal_StfCrdRcd
  AND postemp.PostEmp_Rcd = stfcrd.StfCrd_PostEmpRcd
  AND prod.Prod_Rcd = deal.Deal_ProdRcd
  AND zrp.zrpstf_rcd = deal.Deal_StfCrdRcd
  AND deal_date BETWEEN @vardt5 AND @vardt6
 
UNION
 
SELECT
  Deal_StfCrdRcd AS MerchantID, stfcrd.stfcrd_fio AS MerchNm, postemp.postemp_nm AS Pos
  , SUM(deal_col) AS QSold, SUM(deal_col * prod_prc) AS Itog
  , ROUND(SUM((deal_col * prod_prc) * zrp_comm)) AS Comm, zrp_okl AS OKL
  , ROUND(SUM((deal_col * prod_prc) * zrp_comm) + zrp_okl) AS Salary
  , MONTHNAME(@vardt2) AS Mnh
FROM
  deal, stfcrd, postemp, prod, zrp
WHERE
  Deal_StfCrdRcd = @varid3 AND stfcrd.StfCrd_Rcd = deal.Deal_StfCrdRcd
  AND postemp.PostEmp_Rcd = stfcrd.StfCrd_PostEmpRcd
  AND prod.Prod_Rcd = deal.Deal_ProdRcd
  AND zrp.zrpstf_rcd = deal.Deal_StfCrdRcd
  AND deal_date BETWEEN @vardt5 AND @vardt6
 
UNION
 
SELECT
  Deal_StfCrdRcd AS MerchantID, stfcrd.stfcrd_fio AS MerchNm, postemp.postemp_nm AS Pos
  , SUM(deal_col) AS QSold, SUM(deal_col * prod_prc) AS Itog
  , ROUND(SUM((deal_col * prod_prc) * zrp_comm)) AS Comm, zrp_okl AS OKL
  , ROUND(SUM((deal_col * prod_prc) * zrp_comm) + zrp_okl) AS Salary
  , MONTHNAME(@vardt2) AS Mnh
FROM
  deal, stfcrd, postemp, prod, zrp
WHERE
  Deal_StfCrdRcd = @varid4 AND stfcrd.StfCrd_Rcd = deal.Deal_StfCrdRcd
  AND postemp.PostEmp_Rcd = stfcrd.StfCrd_PostEmpRcd
  AND prod.Prod_Rcd = deal.Deal_ProdRcd
  AND zrp.zrpstf_rcd = deal.Deal_StfCrdRcd
  AND deal_date BETWEEN @vardt5 AND @vardt6

Источник

Subquery returns more than 1 row mysql ошибка

А вот еще интересные материалы: