CODEWITHSUNDEEP
mysqlgroup-by
DavSev
DavSev

Reputation: 1111

MySql query graph data group - filter by data in column

i have two tables that i want to connect,
this is table 1 "calendar"

enter image description here

this is table 2 "lead"
enter image description here

I have this query 

SELECT calendar.datefield AS DATE, IFNULL( count( lead.insertDate ) , 0 ) AS task FROM lead RIGHT JOIN calendar ON ( DATE( lead.insertDate ) = calendar.datefield ) GROUP BY DATE LIMIT 30

It works great, this is the result i get:

enter image description here

the thing is that i want to filter the data by lead.lpid. I tried to add at the end of the query 'WHERE lead.lpid = x' and add at the begining of the query call for lead.lpid data like this: 

SELECT lead.lpid, calendar.datefield AS DATE, 
        IFNULL( count( lead.insertDate ) , 0 ) AS task
        FROM lead 
        RIGHT JOIN calendar ON ( DATE( lead.insertDate ) = calendar.datefield ) 
        GROUP BY DATE LIMIT 30 WHERE lead.lpid =  9

but i get this error 

.....'WHERE lead.lpid =  9' at line 5

I am kind'a lost. any help would be great!

Here is my SQL fiddle, I have tried query: SQLFiddle

Upvotes: 0

Views: 94

Answers (3)

Amol Solanke
Amol Solanke

Reputation: 777

SELECT lead.lpid,
       calendar.datefield AS DATE,
       IFNULL(count(lead.insertDate), 0) AS task
FROM lead
RIGHT JOIN calendar ON (DATE(lead.insertDate) = calendar.datefield)
GROUP BY DATE
HAVING lead.lpid = 9 LIMIT 30

use sql formater online: sqlformat

Upvotes: 0

Pathik Vejani
Pathik Vejani

Reputation: 4501

place of Where is wrong:

SELECT lead.lpid, calendar.datefield AS DATE, 
        IFNULL( count( lead.insertDate ) , 0 ) AS task
        FROM lead 
        RIGHT JOIN calendar ON ( DATE( lead.insertDate ) = calendar.datefield )
        WHERE lead.lpid =  9 
        GROUP BY DATE LIMIT 30

Note: WHERE must be before GROUP BY

New Query as you want:

SELECT lead.lpid, calendar.datefield AS DATE, 
        IFNULL( count( lead.insertDate ) , 0 ) AS task
        FROM lead 
        RIGHT JOIN calendar ON ( DATE( lead.insertDate ) = calendar.datefield )
        AND lead.lpid =  9 
        GROUP BY DATE LIMIT 30

Upvotes: 1

Vipin Jain
Vipin Jain

Reputation: 3756

SELECT lead.lpid, calendar.datefield AS DATE, 
        IFNULL( count( lead.insertDate ) , 0 ) AS task
        FROM lead 
        RIGHT JOIN calendar ON ( DATE( lead.insertDate ) = calendar.datefield ) 
        WHERE lead.lpid =  9 GROUP BY DATE LIMIT 30

Upvotes: 1

Related Questions