CODEWITHSUNDEEP
mysqlsqlselectgroup-byhaving
user2881809
user2881809

Reputation:

how correctly execute query?

STRUCTURE TABLES AND ERROR WHEN EXECUTE QUERY ON SQLFIDDLE

QUERY:

SELECT 
            n.Type as Type,
            n.UserIdn as UserIdn,
            u.Username as Username,
            n.NewsIdn as NewsIdn,
            n.Header as Header,
            n.Text as Text,
            n.Tags as Tags,
            n.ImageLink as ImageLink,
            n.VideoLink as VideoLink,
            n.DateCreate as DateCreate,
            SUM(l.Type = 'up') as Uplikes, 
            SUM(l.Type = 'down') as Downlikes,
            (SUM(l.Type = 'up') - SUM(l.Type = 'down')) as SumLikes
        FROM News n 
            INNER JOIN Users u ON n.UserIdn = u.UserIdn 
            LEFT JOIN Likes l ON n.NewsIdn = l.NewsIdn
            WHERE SumLikes > 20 AND n.DateCreate < STR_TO_DATE('2014-01-03 14:17:43', '%Y-%m-%d %H:%i:%s')
        GROUP BY n.id
        ORDER by SumLikes DESC

When i use query i get error:

Unknown column 'SumLikes' in 'where clause'...

I know that error in 'SumLikes' but how correctly make sql query?

Tell me please how make it?

Upvotes: 2

Views: 76

Answers (4)

Saharsh Shah
Saharsh Shah

Reputation: 29051

Try this: 

SELECT 
    n.Type AS TYPE,
    n.UserIdn AS UserIdn,
    u.Username AS Username,
    n.NewsIdn AS NewsIdn,
    n.Header AS Header,
    n.Text AS TEXT,
    n.Tags AS Tags,
    n.ImageLink AS ImageLink,
    n.VideoLink AS VideoLink,
    n.DateCreate AS DateCreate,
    SUM(l.Type = 'up') AS Uplikes, 
    SUM(l.Type = 'down') AS Downlikes,
    (SUM(l.Type = 'up') - SUM(l.Type = 'down')) AS SumLikes
FROM News n 
INNER JOIN Users u ON n.UserIdn = u.UserIdn 
LEFT JOIN Likes l ON n.NewsIdn = l.NewsIdn
WHERE n.DateCreate < '2014-01-03 14:17:43'
GROUP BY n.id HAVING SumLikes > 20 
ORDER BY SumLikes DESC

Upvotes: 0

Melon
Melon

Reputation: 883

You cant use aggregate fields like sum() in where clause, you shuld use having,

the end of your query could be like: 

GROUP BY n.id
HAVING SumLikes > 20
ORDER by SumLikes DESC

ofcourse remove SumLikes > 20 in your where.

Check this out for more information: MySQL Extensions to GROUP BY

Upvotes: 1

Orel Eraki
Orel Eraki

Reputation: 12196

When adding a column an alias, we need to address it diffrently.

Remove SumLikes > 20 and add an extra command after GROUP BY

Example:

HAVING SumLikes > 20

Upvotes: 1

Mad Dog Tannen
Mad Dog Tannen

Reputation: 7242

Wrap it in a subquery

SELECT * FROM
(
SELECT 
    n.Type as Type,
    n.UserIdn as UserIdn,
    u.Username as Username,
    n.NewsIdn as NewsIdn,
    n.Header as Header,
    n.Text as Text,
    n.Tags as Tags,
    n.ImageLink as ImageLink,
    n.VideoLink as VideoLink,
    n.DateCreate as DateCreate,
    SUM(l.Type = 'up') as Uplikes, 
    SUM(l.Type = 'down') as Downlikes,
    (SUM(l.Type = 'up') - SUM(l.Type = 'down')) as SumLikes
FROM News n 
    INNER JOIN Users u ON n.UserIdn = u.UserIdn 
    LEFT JOIN Likes l ON n.NewsIdn = l.NewsIdn
    WHERE n.DateCreate < STR_TO_DATE('2014-01-03 14:17:43', '%Y-%m-%d %H:%i:%s')
GROUP BY n.id
) Sub
WHERE Sub.SumLikes > 20
ORDER by Sub.SumLikes DESC

Upvotes: 0

Related Questions