There are two requests made to the database and glued into one, but I can’t group it so that it does not throw out some of the data.

enter image description here

And get after grouping such a result that were in the column potr and kolob and empty values ​​and filled.

result

BEGIN /* SELECT DISTINCT reg1,grafik, potr,kolob FROM (*/ SELECT r.id_reg AS idreg,r.regnames AS reg1,r.subord AS subr, r.subordend AS sebr, (SELECT COUNT( * ) FROM kartosn k WHERE k.obdate > 2006-01-01 AND k.subord>=subr AND k.subord<sebr ) AS grafik, r.test AS potr,r.test AS kolob FROM region r WHERE r.id_reg>1 AND r.id_reg<>10 AND r.id_reg<>20 AND r.id_reg<>30 AND r.id_reg<>40 AND r.id_reg<>12 AND r.id_reg<>13 UNION SELECT idreg, reg1, (SELECT r.test FROM region r WHERE r.id_reg=1 ) AS t2, (SELECT r.test FROM region r WHERE r.id_reg=1 ) AS t1, (SELECT COUNT( * ) FROM kartosn k WHERE k.obdate > 2006-01-01 AND k.subord>=subr AND k.subord<sebr ) AS grafik, (SELECT COUNT( idreg )) AS potr, (SELECT (SUM(ografik)+SUM(cgrafik)) FROM region r WHERE idreg=r.id_reg ) AS kolob FROM ( SELECT k.obdate, k.kartel_id as id,r.id_reg AS idreg, r.regnames AS reg1, r.subord AS subr ,r.subordend AS sebr, (SELECT COUNT( * ) AS N FROM objects o WHERE ografik=1 AND o.kartel_id =id) AS ografik, (SELECT COUNT( * ) AS M FROM ctp WHERE `cgrafik` =1 AND kartel_id = id) as cgrafik, (SELECT DATE_FORMAT( o.pasobdate, "%d.%m.%Y" ) FROM ozp o WHERE o.kartel_id = id AND o.pasobdate>"0000-00-00" ) AS pasobdate FROM kartosn k, `region` r, auth a WHERE k.obdate > "2009-01-01" AND k.obdate <date_ob AND( SELECT COUNT(*) FROM ozp o WHERE o.kartel_id = k.kartel_id AND o.pasobdate>"0000-00-00")<>1 AND a.`subord` = k.`subord` AND k.`subord`=r.`subord` GROUP BY id ORDER BY reg1 ) AS ff GROUP BY reg1 /* )AS tyyyyy ORDER BY reg1 */ ; END
  • Would you even have led the queries themselves. But it is not at all clear what you call “two requests glued into one”, glued together with glue, BF-2 or maybe all the same union, or maybe this kind of tricky join, go and guess ... - Mike
  • Requests glued stupidly UNION - Yura Uleichuk
  • Well, so that with union they would give the first result, you must try hard, obviously write NULL columns in the queries. it may just be worth asking the correct lists of output columns in the queries, which would give out only the necessary data and in the right columns - Mike
  • The columns potr and kolob in the first query were specially created to be empty so that when combined into them, to record data from the second query - Yura Uleichuk
  • Seems to be beginning to understand ... so make a group by of all this and take let's say max () from the speakers - Mike

1 answer 1

 select id_reg, reg1, max(grafic) as grafic, max(potr) port, max(kolob) kloob from ( ваш-большой-запрос ) A group by id_reg, reg1

PS (a bit oftopik): I can offer the following query text optimization:

  1. r.id_reg>1 AND r.id_reg<>10 AND r.id_reg<>20 ... rewrite as r.id_reg>1 AND r.id_reg not in(10,20,30,40,12,13) - just shorter and clearer.

  2. The beginning of the second part of the main query is simplified as follows:

     SELECT idreg, reg1, NULL AS t2, NULL AS t1, (SELECT COUNT( * ) FROM kartosn k WHERE k.obdate > 2006-01-01 AND k.subord>=subr AND k.subord<sebr ) AS grafik, COUNT(idreg) AS potr, SUM(ografik)+SUM(cgrafik) AS kolob
  3. In the deepest subquery of the second part, columns such as subr, sebr, pasobdate are selected (as a separate subquery), but they are not used anywhere else. you can remove them, unless of course they are needed in the outer layers of the query in the future.
  • Try instead of UNION to write UNION ALL - igaraev