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.