Creating a MySQL view using the COUNT aggregate

user_120888 0
Viewed 54

I'm creating a standard COUNT query with a GROUP BY clause.

CREATE VIEW view1 AS
SELECT
    t2.column3                  ,
    t1.id AS t1_id              ,
    t2.column4                  ,
    COUNT ( t3.id ) AS t3_count , -- Error here
    COUNT ( t4.id ) AS t4_count
FROM      table1 t1
LEFT JOIN table2 t2 ON t2.column1 = 25 AND
                       t2.column2 = t1.id
LEFT JOIN table3 t3 ON t3.column1 = t1.id
LEFT JOIN table4 t4 ON t4.column1 = t1.id
GROUP BY t2.column3 ,
         t1.id      ,
         t2.column4 ;

But this gives an error:

Error Code: 1630. FUNCTION mydb.COUNT does not exist.

Why does MySQL think I'm trying to invoke a user-defined-function in my database?
Does it not recognize COUNT as a built-in aggregate operation?

I also tried COUNT ( t3.* ) but that gave a generic syntax error.
I can't use COUNT ( * ) because there are multiple LEFT JOINS being counted at the same time.

count function mysql sql
1 Answers

I would remove spaces:

CREATE VIEW view1 AS
SELECT
    t2.column3                  ,
    t1.id AS t1_id              ,
    t2.column4                  ,
    COUNT(t3.id) AS t3_count , -- Error here
    COUNT(t4.id) AS t4_count
FROM      table1 t1
LEFT JOIN table2 t2 ON t2.column1 = 25 AND
                       t2.column2 = t1.id
LEFT JOIN table3 t3 ON t3.column1 = t1.id
LEFT JOIN table4 t4 ON t4.column1 = t1.id
GROUP BY t2.column3 ,
         t1.id      ,
         t2.column4 ;

db<>fiddle demo

SELECT COUNT(*)
-- 1

SELECT COUNT (*)
-- You have an error in your SQL syntax; 

SELECT COUNT ( * )
-- You have an error in your SQL syntax;

Addendum:

As @Paul Spiegel commented this behaviour could be altered:

set session sql_mode = concat(@@sql_mode, ',IGNORE_SPACE');

SELECT COUNT ( * )
-- 1

db<>fiddle demo2

user_50708790
Related