Problem with subquery

Miguel61 · February 20, 2014, 9:23am

Hi all, I need your appreciated help.

I need this output:

+----------+--------+-----+------------------------+------------+
| COUNT(*) | Combo  | Q   | text                   | LastDate   |
+----------+--------+-----+------------------------+------------+
|     5954 | Combo1 |   3 | to be treated          | 20/02/2014 |
|     5954 | Combo1 | 118 | regular                | 20/02/2014 |
|     5954 | Combo1 |   6 | verification Required  | 20/02/2014 |
|     5954 | Combo1 |   9 | required control       | 20/02/2014 |
|     7171 | Combo2 |   7 | regular                | 20/02/2014 |
|     7171 | Combo2 |   1 | verification Required  | 20/02/2014 |
|     7171 | Combo2 |  42 | required control       | 20/02/2014 |
|     7888 | Combo3 | 379 | regular                | 20/02/2014 |
|     7888 | Combo3 |  54 | verification Required  | 20/02/2014 |
|     7888 | Combo3 |  78 | required control       | 20/02/2014 |
|     1942 | Combo4 | 224 | regular                | 20/02/2014 |
|     1942 | Combo4 |  26 | verification Required  | 20/02/2014 |
+----------+--------+-----+------------------------+------------+
12 rows in set

And this is my step to step, first sql query:

mysql> SELECT
	CASE
WHEN LEFT (area, 2) = '2I' THEN
	'Combo1'
WHEN LEFT (area, 2) = '2M' THEN
	'Combo2'
WHEN LEFT (area, 2) = '2O' THEN
	'Combo3'
WHEN LEFT (area, 2) = '2S' THEN
	'Combo4'
ELSE
	'tot'
END AS Combo,
 COUNT(*)
FROM
	doTable
GROUP BY
	LEFT (area, 2) WITH ROLLUP;
+--------+----------+
| Combo  | COUNT(*) |
+--------+----------+
| Combo1 |     5954 |
| Combo2 |     7171 |
| Combo3 |     7888 |
| Combo4 |     1942 |
| Combo4 |    22955 |
+--------+----------+
5 rows in set

Next sql query:

mysql> SELECT
	CASE
WHEN LEFT (area, 2) = '2I' THEN
	'Combo1'
WHEN LEFT (area, 2) = '2M' THEN
	'Combo2'
WHEN LEFT (area, 2) = '2O' THEN
	'Combo3'
ELSE
	'Combo4'
END AS Combo,
 COUNT(text) AS q,
 text AS text,
 DATE_FORMAT(CURRENT_DATE(), '%d/%m/%Y') AS LastDate
FROM
	doTable
WHERE
	text IS NOT NULL
AND text > ''
GROUP BY
	Combo,
	text;

+--------+-----+------------------------+------------+
| Combo  | q   | text                   | LastDate   |
+--------+-----+------------------------+------------+
| Combo1 |   3 | to be treated          | 20/02/2014 |
| Combo1 | 118 | regular                | 20/02/2014 |
| Combo1 |   6 | verification Required  | 20/02/2014 |
| Combo1 |   9 | required control       | 20/02/2014 |
| Combo2 |   7 | regular                | 20/02/2014 |
| Combo2 |   1 | verification Required  | 20/02/2014 |
| Combo2 |  42 | required control       | 20/02/2014 |
| Combo3 | 379 | regular                | 20/02/2014 |
| Combo3 |  54 | verification Required  | 20/02/2014 |
| Combo3 |  78 | required control       | 20/02/2014 |
| Combo4 | 224 | regular                | 20/02/2014 |
| Combo4 |  26 | verification Required  | 20/02/2014 |
+--------+-----+------------------------+------------+
12 rows in set

Final sql query:

mysql> SELECT
	COUNT(*),
	Combo,
	Q,
	text,
	LastDate
FROM
	(
		SELECT
			CASE
		WHEN LEFT (area, 2) = '2I' THEN
			'Combo1'
		WHEN LEFT (area, 2) = '2M' THEN
			'Combo2'
		WHEN LEFT (area, 2) = '2O' THEN
			'Combo3'
		ELSE
			'Combo4'
		END AS Combo,
		COUNT(text) AS q,
		text AS text,
		DATE_FORMAT(CURRENT_DATE(), '%d/%m/%Y') AS LastDate
	FROM
		doTable
	WHERE
		text IS NOT NULL
	AND text > ''
	GROUP BY
		Combo,
		text
	) q
WHERE
	q.text IS NOT NULL
AND q.text > ''
GROUP BY
	Combo,
	text;
+----------+--------+-----+------------------------+------------+
| COUNT(*) | Combo  | Q   | text                   | LastDate   |
+----------+--------+-----+------------------------+------------+
|        1 | Combo1 |   3 | to be treated          | 20/02/2014 |
|        1 | Combo1 | 118 | regular                | 20/02/2014 |
|        1 | Combo1 |   6 | verification Required  | 20/02/2014 |
|        1 | Combo1 |   9 | required control       | 20/02/2014 |
|        1 | Combo2 |   7 | regular                | 20/02/2014 |
|        1 | Combo2 |   1 | verification Required  | 20/02/2014 |
|        1 | Combo2 |  42 | required control       | 20/02/2014 |
|        1 | Combo3 | 379 | regular                | 20/02/2014 |
|        1 | Combo3 |  54 | verification Required  | 20/02/2014 |
|        1 | Combo3 |  78 | required control       | 20/02/2014 |
|        1 | Combo4 | 224 | regular                | 20/02/2014 |
|        1 | Combo4 |  26 | verification Required  | 20/02/2014 |
+----------+--------+-----+------------------------+------------+
12 rows in set

The output is wrong… can you help me?
Thank you in advance for any help.

DarthGuido · February 20, 2014, 12:44pm

I don’t see where the third query is coming from?
Why don’t you do a join of the first two queries, I think that’ll give what you’re looking for.

Miguel61 · February 20, 2014, 1:26pm

I’m sorry I don’t understand your help …

r937 · February 20, 2014, 1:46pm

he means you should join the second query to the first query

like this –

SELECT query1.combo_count AS `COUNT(*)`
     , query1.combo
     , query2.q
     , query2.text
     , query2.lastdate
  FROM [COLOR="#FF0000"]( SELECT CASE LEFT(area, 2)
                     WHEN '2I' THEN 'Combo1'
                     WHEN '2M' THEN 'Combo2'
                     WHEN '2O' THEN 'Combo3'
                     WHEN '2S' THEN 'Combo4'
                               ELSE 'tot' END AS Combo
              , COUNT(*) AS combo_count
           FROM doTable
         GROUP 
             BY LEFT(area, 2) WITH ROLLUP ) AS query1[/COLOR]
LEFT OUTER
  JOIN [COLOR="#0000FF"]( SELECT CASE LEFT(area, 2)
                     WHEN '2I' THEN 'Combo1'
                     WHEN '2M' THEN 'Combo2'
                     WHEN '2O' THEN 'Combo3'
                               ELSE 'Combo4' END AS Combo
              , COUNT(text) AS q
              , text AS text
              , DATE_FORMAT(CURRENT_DATE(),'%d/%m/%Y') AS LastDate
           FROM doTable
          WHERE text &gt; ''
         GROUP 
             BY Combo
              , text ) AS query2[/COLOR]

    ON [COLOR="#0000FF"]query2.combo [/COLOR]= [COLOR="#FF0000"]query1.combo[/COLOR]

Miguel61 · February 20, 2014, 2:36pm

Many thanks for help and now I’ve this output:

+----------+--------+-----+------------------------+------------+
| COUNT(*) | Combo  | Q   | text                   | LastDate   |
+----------+--------+-----+------------------------+------------+
|     5954 | Combo1 |   3 | to be treated          | 20/02/2014 |
|     5954 | Combo1 | 118 | regular                | 20/02/2014 |
|     5954 | Combo1 |   6 | verification Required  | 20/02/2014 |
|     5954 | Combo1 |   9 | required control       | 20/02/2014 |
|     7171 | Combo2 |   7 | regular                | 20/02/2014 |
|     7171 | Combo2 |   1 | verification Required  | 20/02/2014 |
|     7171 | Combo2 |  42 | required control       | 20/02/2014 |
|     7888 | Combo3 | 379 | regular                | 20/02/2014 |
|     7888 | Combo3 |  54 | verification Required  | 20/02/2014 |
|     7888 | Combo3 |  78 | required control       | 20/02/2014 |
|     1942 | Combo4 | 224 | regular                | 20/02/2014 |
|     1942 | Combo4 |  26 | verification Required  | 20/02/2014 |
|    22955 | Combo4 | 224 | regular                | 20/02/2014 |
|    22955 | Combo4 |  26 | verification Required  | 20/02/2014 |
+----------+--------+-----+------------------------+------------+

In the final output I’ve in the column Combo, Q and test the last two lines are duplicated, why?:

|     1942 | Combo4 | 224 | regular                | 20/02/2014 |
|     1942 | Combo4 |  26 | verification Required  | 20/02/2014 |
|    22955 | Combo4 | 224 | regular                | 20/02/2014 |
|    22955 | Combo4 |  26 | verification Required  | 20/02/2014 |
+----------+--------+-----+------------------------+------------+

r937 · February 20, 2014, 3:14pm

most likely this is due to WITH ROLLUP

see Combo4 22955 in your first post

Miguel61 · February 20, 2014, 3:27pm

Yes right, Sir … but I think in this output WITH ROLLUP :

+----------+--------+-----+------------------------+------------+
| COUNT(*) | Combo  | Q   | text                   | LastDate   |
+----------+--------+-----+------------------------+------------+
|     5954 | Combo1 |   3 | to be treated          | 20/02/2014 |
|     5954 | Combo1 | 118 | regular                | 20/02/2014 |
|     5954 | Combo1 |   6 | verification Required  | 20/02/2014 |
|     5954 | Combo1 |   9 | required control       | 20/02/2014 |
|     7171 | Combo2 |   7 | regular                | 20/02/2014 |
|     7171 | Combo2 |   1 | verification Required  | 20/02/2014 |
|     7171 | Combo2 |  42 | required control       | 20/02/2014 |
|     7888 | Combo3 | 379 | regular                | 20/02/2014 |
|     7888 | Combo3 |  54 | verification Required  | 20/02/2014 |
|     7888 | Combo3 |  78 | required control       | 20/02/2014 |
|     1942 | Combo4 | 224 | regular                | 20/02/2014 |
|     1942 | Combo4 |  26 | verification Required  | 20/02/2014 |
|    22955 | tot    | 947 |                        |            |
+----------+--------+-----+------------------------+------------+

where:

SELECT CASE LEFT(area, 2)
                     WHEN '2I' THEN 'Combo1'
                     WHEN '2M' THEN 'Combo2'
                     WHEN '2O' THEN 'Combo3'
                     WHEN '2S' THEN 'Combo4'
                               ELSE 'tot' END AS Combo

r937 · February 20, 2014, 5:11pm

yes, exactly the reason

you understand now how to join subqueries, so fixing that problem should be easy

DaveMaxwell · February 20, 2014, 5:37pm

The case for the 2nd query is inconsistent with the first -

This
WHEN ‘2O’ THEN ‘Combo3’
ELSE ‘Combo4’ END AS Combo

Should be
WHEN ‘2O’ THEN ‘Combo3’
WHEN ‘2S’ THEN 'Combo4
ELSE ‘tot’ END AS Combo

r937 · February 20, 2014, 6:12pm

indeed

i wasn’t going to mention those discepancies unless miguel came back with a plea for more help understanding ROLLUP

DaveMaxwell · February 20, 2014, 7:17pm

Ah. I have never used ROLLUP before (should have based on quick search), but since the case was wrong, the join would be wrong which would guarantee ROLLUP would be wrong.

Miguel61 · February 21, 2014, 7:35am

I’ve changed the query:

SELECT query1.combo_count AS `COUNT(*)`
     , query1.combo
     , query2.q
     , query2.text
     , query2.lastdate
  FROM ( SELECT CASE LEFT(area, 2)
                     WHEN '2I' THEN 'Combo1'
                     WHEN '2M' THEN 'Combo2'
                     WHEN '2O' THEN 'Combo3'
                     WHEN '2S' THEN 'Combo4'
                               ELSE 'tot' END AS Combo
              , COUNT(*) AS combo_count
           FROM doTable
         GROUP 
             BY LEFT(area, 2) WITH ROLLUP ) AS query1
LEFT OUTER
  JOIN ( SELECT CASE LEFT(area, 2)
                     WHEN '2I' THEN 'Combo1'
                     WHEN '2M' THEN 'Combo2'
                     WHEN '2O' THEN 'Combo3'
                     WHEN '2S' THEN 'Combo4'
                               ELSE 'tot' END AS Combo
              , COUNT(text) AS q
              , text AS text
              , DATE_FORMAT(CURRENT_DATE(),'%d/%m/%Y') AS LastDate
           FROM doTable
          WHERE text &gt; ''
         GROUP 
             BY Combo
              , text ) AS query2

    ON query2.combo = query1.combo

But the output not change…

+----------+--------+-----+------------------------+------------+
| COUNT(*) | Combo  | Q   | text                   | LastDate   |
+----------+--------+-----+------------------------+------------+
|     5954 | Combo1 |   3 | to be treated          | 20/02/2014 |
|     5954 | Combo1 | 118 | regular                | 20/02/2014 |
|     5954 | Combo1 |   6 | verification Required  | 20/02/2014 |
|     5954 | Combo1 |   9 | required control       | 20/02/2014 |
|     7171 | Combo2 |   7 | regular                | 20/02/2014 |
|     7171 | Combo2 |   1 | verification Required  | 20/02/2014 |
|     7171 | Combo2 |  42 | required control       | 20/02/2014 |
|     7888 | Combo3 | 379 | regular                | 20/02/2014 |
|     7888 | Combo3 |  54 | verification Required  | 20/02/2014 |
|     7888 | Combo3 |  78 | required control       | 20/02/2014 |
|     1942 | Combo4 | 224 | regular                | 20/02/2014 |
|     1942 | Combo4 |  26 | verification Required  | 20/02/2014 |
|    22955 | Combo4 | 224 | regular                | 20/02/2014 |
|    22955 | Combo4 |  26 | verification Required  | 20/02/2014 |
+----------+--------+-----+------------------------+------------+

r937 · February 21, 2014, 12:14pm

see post #6

Miguel61 · February 21, 2014, 2:39pm

Okey, I see… This means that I can’t have the output in #1 ?

r937 · February 21, 2014, 6:38pm

no

it means that you can have it, if you fix the problem

Miguel61 · February 22, 2014, 2:22pm

Okey thank you… this is the new version of query and new output … in the column COUNT(*) I’ve the total right … but why I don’t see the total in the column Q ? And e.g. how to replace 2I with Combo1 ?

SELECT query1.combo_count AS `COUNT(*)`
     , IFNULL(query1.combo, 'Tot') AS `Combo`,
     , query2.q
     , query2.text
     , query2.lastdate
  FROM ( SELECT LEFT(area, 2) AS Combo
              , COUNT(*) AS combo_count
           FROM doTable
         GROUP 
             BY LEFT(area, 2) WITH ROLLUP ) AS query1
LEFT OUTER
  JOIN ( SELECT LEFT(area, 2) AS Combo
              , COUNT(text) AS q
              , text AS text
              , DATE_FORMAT(CURRENT_DATE(),'%d/%m/%Y') AS LastDate
           FROM doTable
          WHERE text &gt; ''
         GROUP 
             BY Combo
              , text ) AS query2
    ON query2.combo = query1.combo;


+----------+--------+--------+------------------------+------------+
| COUNT(*) | Combo  | Q      | text                   | LastDate   |
+----------+--------+--------+------------------------+------------+
|     5954 | 2I     |   3    | to be treated          | 20/02/2014 |
|     5954 | 2I     | 118    | regular                | 20/02/2014 |
|     5954 | 2I     |   6    | verification Required  | 20/02/2014 |
|     5954 | 2I     |   9    | required control       | 20/02/2014 |
|     7171 | 2M     |   7    | regular                | 20/02/2014 |
|     7171 | 2M     |   1    | verification Required  | 20/02/2014 |
|     7171 | 2M     |  42    | required control       | 20/02/2014 |
|     7888 | 2O     | 379    | regular                | 20/02/2014 |
|     7888 | 2O     |  54    | verification Required  | 20/02/2014 |
|     7888 | 2O     |  78    | required control       | 20/02/2014 |
|     1942 | 2S     | 224    | regular                | 20/02/2014 |
|     1942 | 2S     |  26    | verification Required  | 20/02/2014 |
|    22955 | Tot    | (NULL) | (NULL)                 | (NULL)     |
+----------+--------+--------+------------------------+------------+

r937 · February 22, 2014, 5:05pm

because there’s no ROLLUP in query2

r937 · February 22, 2014, 5:07pm

use a CASE expression

Miguel61 · February 22, 2014, 6:03pm

I’m sorry but in this mode the output is:

SELECT query1.combo_count AS `COUNT(*)`
     , IFNULL(query1.combo, 'Tot') AS `Combo`
     , query2.q
     , IFNULL(
		query2.text,
		CONCAT('Tot', ' ' , query1.combo)
	) AS `text`
     , query2.lastdate
  FROM ( SELECT LEFT(area, 2) AS Combo
              , COUNT(*) AS combo_count
           FROM doTable
         GROUP 
             BY LEFT(area, 2) WITH ROLLUP ) AS query1
LEFT OUTER
  JOIN ( SELECT LEFT(area, 2) AS Combo
              , COUNT(text) AS q
              , text AS text
              , DATE_FORMAT(CURRENT_DATE(),'%d/%m/%Y') AS LastDate
           FROM doTable
          WHERE text &gt; ''
         GROUP 
             BY Combo 
              , text WITH ROLLUP ) AS query2
    ON query2.combo = query1.combo;


+----------+--------+--------+------------------------+------------+
| COUNT(*) | Combo  | Q      | text                   | LastDate   |
+----------+--------+--------+------------------------+------------+
|     5954 | 2I     |   3    | to be treated          | 20/02/2014 |
|     5954 | 2I     | 118    | regular                | 20/02/2014 |
|     5954 | 2I     |   6    | verification Required  | 20/02/2014 |
|     5954 | 2I     |   9    | required control       | 20/02/2014 |
|     5954 | 2I     | 136    | Tot 2I                 | 20/02/2014 |

|     7171 | 2M     |   7    | regular                | 20/02/2014 |
|     7171 | 2M     |   1    | verification Required  | 20/02/2014 |
|     7171 | 2M     |  42    | required control       | 20/02/2014 |
|     7171 | 2M     |  50    | Tot 2M                 | 20/02/2014 |

|     7888 | 2O     | 379    | regular                | 20/02/2014 |
|     7888 | 2O     |  54    | verification Required  | 20/02/2014 |
|     7888 | 2O     |  78    | required control       | 20/02/2014 |
|     7888 | 2O     | 511    | Tot 2O                 | 20/02/2014 |

|     1942 | 2S     | 224    | regular                | 20/02/2014 |
|     1942 | 2S     |  26    | verification Required  | 20/02/2014 |
|     1942 | 2S     | 250    | Tot 2S                 | 20/02/2014 |

|    22955 | Tot    | (NULL) | (NULL)                 | (NULL)     |
+----------+--------+--------+------------------------+------------+