Filter table before applying query in MySQL

Question

I have a mysql table called push_message_info that I want to filter and then calculate some results. The query to get the results is:

SELECT p.pending, s.success, t.total
FROM 
    (SELECT count(*) AS pending FROM push_message_info WHERE served < total) AS p,
    (SELECT count(*) AS success FROM push_message_info WHERE served = total) AS s,
    (SELECT count(*) AS total FROM push_message_info) AS t
;

And the thing is I would like to count only the newest entries. Since it has a datetime field called submit_date I've thought something like this would work:

SELECT p.pending, s.success, t.total
FROM 
    (SELECT * FROM push_message_info WHERE DATE(submit_date) > '2017-05-14') AS filtered
    (SELECT count(*) AS pending FROM filtered WHERE served < total) AS p,
    (SELECT count(*) AS success FROM filtered WHERE served = total) AS s,
    (SELECT count(*) AS total FROM filtered) AS t
;

But it throws me the error:

ERROR 1146 (42S02): Table 'unifiedpush.filtered' doesn't exist

How can I filter/limit the original table so that I can apply other queries on that new table?

does it have to be `unifiedpush.push_message_info` and can't be `push_message_info`? — XtremeBaumer, May 15 '17 at 13:58
See: [Why should I provide an MCVE for what seems to me to be a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query) — Strawberry, May 15 '17 at 14:00
@XtremeBaumer it really doesn't matter, I erased it for the sake of simplicity but forget it in the second query, I'll remove it now. — josemigallas, May 15 '17 at 14:02
@Strawberry I don't see what example could I add, since the question is about the query itself not the data. — josemigallas, May 15 '17 at 14:56
Jose, you seem determined to discourage others from helping. It's working. — Strawberry, May 15 '17 at 15:04
Quite the opposite @Strawberry , I must've explained the problem very bad, probably due to my lack of knowledge in this topic, but really I don't understand why I'm discouraging others to help. — josemigallas, May 15 '17 at 15:07
I've suggested a way to explain the problem better. I'm puzzled by your resistance - but presumably you know better than the rest of us. — Strawberry, May 15 '17 at 15:27

score 3 · Answer 1 · edited May 15 '17 at 15:09

3

Your query isn't good in general. Instead of making all these subqueries, you can to that with one simple query.

SELECT 
    SUM(CASE WHEN served < total THEN 1 ELSE 0 END) AS pending,
    SUM(CASE WHEN served = total THEN 1 ELSE 0 END) AS served,
    COUNT(*) as total
FROM push_message_info
WHERE DATE(submit_date) > '2017-05-14';

I've switched counts (except total) with SUM(CASE WHEN ... THEN 1 ELSE 0 END) which let you do conditional counts.

edited May 15 '17 at 15:09

TheLostMind

35,966
12
68
104

answered May 15 '17 at 14:02

Jakub Matczak

15,341
5
46
64

Sorry you are not really answering what it's being asked. – josemigallas May 15 '17 at 14:10
It gives you data that you want. Tell me how it's not answering your question, so I can improve my anwer. – Jakub Matczak May 15 '17 at 15:19
So what I would like to achieve is to filter the table and rename this view as "filtered". Once I got this "intermediate" table, apply queries on it. It's not about the output or the date, it's more about to understand how the subqueries work. – josemigallas May 15 '17 at 15:33

Menachem Bazian · Answer 2 · 2017-05-15T14:33:58.847

1

Well, I can tell you how I have accomplished something like this. Try:

SELECT SUM(IF(served < total, 1, 0)) as pending,
       SUM(IF(served = total,1 ,0)) AS success,
       COUNT(*) as total
  FROM push_message_info
 WHERE DATE(submit_date) > '2017-05-14';

That should give you what you need...

edited May 15 '17 at 14:33

answered May 15 '17 at 14:04

Menachem Bazian

397
2
5
18

This is not really what I want to know. By the way it fails. – josemigallas May 15 '17 at 14:15
Not sure why it fails. I don't have your data so I constructed it by eye but I just did a similar query and the if() function works fine. Could you perhaps elaborate on what you want your output to look like? As I read your SQL and your question, you want a count of [ending, success and total for new submissions... did I miss something? – Menachem Bazian May 15 '17 at 14:27
I just saw the error. Remove the comma after total and it should work. That was a typo. Sorry – Menachem Bazian May 15 '17 at 14:29
So the point isn't really the output. What I'd like to achieve is to "create" an "intermediate" table so I can run some queries on it. This "intermediate" table would be the result of filtering the dates. – josemigallas May 15 '17 at 15:09
Perhaps this might help? It talks about creating a temporary table from a select: [http://stackoverflow.com/questions/5859391/create-a-temporary-table-in-a-select-statement-without-a-separate-create-table](http://stackoverflow.com/questions/5859391/create-a-temporary-table-in-a-select-statement-without-a-separate-create-table). – Menachem Bazian May 15 '17 at 18:21

score 1 · Accepted Answer · edited May 15 '17 at 15:46

1

The problem in your query is the scope of the filtered alias: it is not visible from the other subqueries.

If you really really want to have it, you can create a view:

create view filtered as SELECT * FROM push_message_info WHERE DATE(submit_date) > '2017-05-14';

SELECT p.pending, s.success, t.total
FROM
    (SELECT count(*) AS pending FROM filtered WHERE served < total) AS p,
    (SELECT count(*) AS success FROM filtered WHERE served = total) AS s,
    (SELECT count(*) AS total FROM filtered) AS t;

edited May 15 '17 at 15:46

josemigallas

3,761
1
29
68

answered May 15 '17 at 15:35

Massimiliano

58
7

Thanks dude, although I understand this might not the most efficient approach, now I understand why my query wasn't working. – josemigallas May 15 '17 at 15:44

Filter table before applying query in MySQL

3 Answers3