MySQL Forums
Forum List  »  Newbie

Problem with subquery left outer join
Posted by: angel rivero
Date: February 25, 2014 07:52AM

Hello there, hope in your help.
I need this output
+---------+--------+----------------------+--------+--------------+-------------+
| Combo   | provv  | n_provv              | tot    | %            | The Date    |
+---------+- ------+----------------------+--------+--------------+-------------+
| RDT RAS | regul  | 224                  | 949    | 23.6 %       | 25/02/2014  |
| RDT RAS | ricve  | 26                   | 949    | 2.7 %        | 25/02/2014  |
| RDT UOT | ricon  | 9                    | 3.202  | 0.3 %        | 25/02/2014  |
| RDT UOT | datra  | 3                    | 3.202  | 0.1 %        | 25/02/2014  |
| RDT UOT | regul  | 119                  | 3.202  | 3.7 %        | 25/02/2014  |
| RDT UOT | ricve  | 6                    | 3.202  | 0.2 %        | 25/02/2014  |
| RDT MAL | regul  | 7                    | 3.070  | 0.2 %        | 25/02/2014  |
| RDT MAL | ricve  | 1                    | 3.070  | 0.0 %        | 25/02/2014  |
| RDT MAL | ricon  | 45                   | 3.070  | 1.5 %        | 25/02/2014  |
| RDT PCM | regul  | 379                  | 3.027  | 12.5 %       | 25/02/2014  |
| RDT PCM | ricve  | 54                   | 3.027  | 1.8 %        | 25/02/2014  |
| RDT PCM | ricon  | 78                   | 3.027  | 2.6 %        | 25/02/2014  |
| tot     | NULL   | 951                  | 10.248 | NULL         | NULL        |
+---------+--------+----------------------+--------+--------------+-------------+
And launch this query :
mysql> SELECT
	CASE
WHEN query1.Combo = '1I' THEN
	'RDT UOT'
WHEN query1.Combo = '1M' THEN
	'RDT MAL'
WHEN query1.Combo = '1O' THEN
	'RDT PCM'
WHEN query1.Combo = '1S' THEN
	'RDT RAS'
ELSE
	'Tot'
END AS Combo,
 query2.provv AS `provv`,
 format(
	query2.n_provv,
	0,
	'de_DE'
) AS `n_provv`,
 format(
	query1.combo_count,
	0,
	'de_DE'
) AS `tot`,
 CONCAT(
	ROUND(
		(
			query2.n_provv / query2.query1.combo_count
		) * 100,
		1
	),
	' %'
) AS `%`,
 query2.thedate AS `The Date`
FROM
	(
		SELECT
			LEFT (area, 2) AS Combo,
			COUNT(DISTINCT tel) AS combo_count
		FROM
			`tbl_a`
		GROUP BY
			LEFT (area, 2) WITH ROLLUP
	) AS query1
LEFT OUTER JOIN (
	SELECT
		LEFT (area, 2) AS Combo,
		COUNT(provv) AS n_provv,
		tel,
		provv AS provv,
		DATE_FORMAT(CURRENT_DATE(), '%d/%m/%Y') AS thedate
	FROM
		`tbl_a`
	WHERE
		provv > ''
	GROUP BY
		Combo,
		provv
) AS query2 ON query2.Combo = query1.Combo
ORDER BY
	`tot` DESC;


+---------+--------+----------------------+--------+--------------+-------------+
| combo   | provv  | n_provv              | tot    | %            | The Date    |
+---------+- ------+----------------------+--------+--------------+-------------+
| RDT RAS | regul  | 224                  | 949    | 23.6 %       | 25/02/2014  |
| RDT RAS | ricve  | 26                   | 949    | 2.7 %        | 25/02/2014  |
| RDT UOT | ricon  | 9                    | 3.202  | 0.3 %        | 25/02/2014  |
| RDT UOT | datra  | 3                    | 3.202  | 0.1 %        | 25/02/2014  |
| RDT UOT | regul  | 119                  | 3.202  | 3.7 %        | 25/02/2014  |
| RDT UOT | ricve  | 6                    | 3.202  | 0.2 %        | 25/02/2014  |
| RDT MAL | regul  | 7                    | 3.070  | 0.2 %        | 25/02/2014  |
| RDT MAL | ricve  | 1                    | 3.070  | 0.0 %        | 25/02/2014  |
| RDT MAL | ricon  | 45                   | 3.070  | 1.5 %        | 25/02/2014  |
| RDT PCM | regul  | 379                  | 3.027  | 12.5 %       | 25/02/2014  |
| RDT PCM | ricve  | 54                   | 3.027  | 1.8 %        | 25/02/2014  |
| RDT PCM | ricon  | 78                   | 3.027  | 2.6 %        | 25/02/2014  |
| tot     | NULL   | NULL                 | 10.248 | NULL         | NULL        |
+---------+--------+----------------------+--------+--------------+-------------+
13 rows in set
Why I don't have the sum of the column `n_provv` in my output?
Can you help me?
Thansk you in adavnce for any help.



Edited 3 time(s). Last edit at 02/25/2014 07:55AM by angel rivero.

Options: ReplyQuote


Subject
Written By
Posted
Problem with subquery left outer join
February 25, 2014 07:52AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.