CODEWITHSUNDEEP
mysqlwhere-clausebetween
killstreet
killstreet

Reputation: 1332

Mysql query not working with WHERE BETWEEN

I was trying to get some data from my database, however I am currently only getting errors.

The query I am trying to do =

SELECT 
   count(id), 
   day(created_at), 
   year(created_at), 
   month(created_at) 
FROM 
   `orders` 
WHERE 
   day(created_at) = BETWEEN 1 AND 7 month(created_at) = 6 
   AND year(created_at) = 2014 
   AND company_id = 1 
group by 
   year(created_at), 
   month(created_at), 
   day(created_at)

The days between 1 and 7 will be the days sunday trough saturday to get all orders in that week.

Thanks in advance.

Upvotes: 0

Views: 81

Answers (5)

Mubo
Mubo

Reputation: 1070

   You have SQL Syntax errors , MISSING "AND" and "BETWEEN" not used correctly.
   Try the following:-


    SELECT COUNT(id), day(created_at), year(created_at), month(created_at)
    FROM  
   `orders`
    WHERE day(created_at)  BETWEEN 1 AND 7 
    AND  month(created_at) = 6 
    AND year(created_at) = 2014
    AND company_id = 1 
    GROUP BY year(created_at), month(created_at), day(created_at);

Upvotes: 0

mzm
mzm

Reputation: 1

you must trim = from your query i write it for you again:

SELECT count(id), day(created_at), year(created_at), month(created_at) 
FROM `orders`
WHERE (day(created_at) BETWEEN 1 AND 7)
AND (month(created_at) = 6)
AND (year(created_at) = 2014)
AND (company_id = 1)
group by year(created_at), month(created_at), day(created_at)

Upvotes: 0

Sadikhasan
Sadikhasan

Reputation: 18600

Your query syntax is wrong near month(created_at) = 

SELECT count(id),
       day(created_at),
       year(created_at),
       month(created_at)
FROM `orders`
WHERE day(created_at) BETWEEN 1 AND 7 
  AND month(created_at) = 6
  AND year(created_at) = 2014
  AND company_id = 1
GROUP BY year(created_at),
         month(created_at),
         day(created_at)

Upvotes: 0

tjati
tjati

Reputation: 6079

SELECT count(id), day(created_at), year(created_at), month(created_at) 
FROM `orders` 
WHERE day(created_at) BETWEEN 1 AND 7
AND month(created_at) = 6 
AND year(created_at) = 2014 
AND company_id = 1 
group by year(created_at), month(created_at), day(created_at)

The AND before month(created_at) as missed.

Upvotes: 1

Code Lღver
Code Lღver

Reputation: 15603

Your query is wrong. this should be:

  SELECT count(id), day(created_at), year(created_at), month(created_at) 
      FROM `orders` WHERE day(created_at) BETWEEN 1 AND 7 
            AND month(created_at) = 6 AND year(created_at) = '2014' 
            AND company_id = 1 
        group by year(created_at), month(created_at), day(created_at)

You are using = and BETWEEN at same place which is incorrect.

Upvotes: 1

Related Questions