Why is in MySQL every valid SELECT not a valid VIEW?

I want to get a count, grouped by day. The following SELECT works fine:

  SELECT COUNT( time_end ), 
         time_end
    FROM main
GROUP BY DAY( time_end )

I copy/pasted that into the VIEW creation form of phpmyadmin, and get this error:

"#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'count(time开发者_高级运维_end)) AS SELECT count( time_end ) , time_end FROM main GROUP BY d' at line 4"

Here is the complete SQL generated from phpmyadmin's form:

CREATE ALGORITHM = UNDEFINED VIEW `count by day` (
  time_end,
  count( time_end )
) AS SELECT COUNT( time_end ), 
            time_end
       FROM main
   GROUP BY DAY( time_end ) 

What's wrong? Why is a valid SELECT not automatically a valid VIEW creation? Thanks.

---

Don't know where phpMyAdmin is getting that syntax. Use:

CREATE VIEW COUNT_BY_DAY AS
  SELECT COUNT( time_end ), 
         time_end
    FROM main
GROUP BY DAY( time_end )

Be aware that you are relying on MySQL's hidden column functionality - the time_end value will be arbitrary (can't be relied on to always return the same value as the number of values to choose from increases). It also can't be ported to most databases, you'll have to re-write it.

Also, you're counts are going to be skewed for days 28 - 31 if you aren't limiting by month. Not all months have 31 days.

---

The only thing wrong with your query is that you have a column name that is not back-ticked.

CREATE ALGORITHM = UNDEFINED VIEW count by day (
`count( time_end )` # you needed to put this in backticks
time_end,
) AS SELECT COUNT( time_end ),
time_end
FROM main
GROUP BY DAY( time_end )

For what it's worth, you have the column names around the wrong way.

The syntax you are using is EXPLICIT NAMING of view column names. It allows you to give names to column that would otherwise be derived from the query columns, e.g.

CREATE VIEW X AS
SELECT time_end, other1
FROM main

:: X contains the columns `time_end` and `other1`

CREATE VIEW X ( TheStopTime, DataPoint ) AS
SELECT time_end, other1
FROM main

:: X contains the columns `TheStopTime` and `DataPoint`