MySQL How to include in where two clause?

SELECT 
  cms_module_ladu_batch.id AS batch_id, 
  name, t1, cms_module_ladu_batch.date_changed AS date_ch, 
  t13, t2, t3, t4, bkuup, date_modified, date_created, 
  kogused.aadress AS aadress, 
  kogused.kogus AS kogus, 
  SUM(kogused.kogus) AS total 
FROM 
  cms_module_ladu_batch 
  LEFT JOIN kogused 
    ON cms_module_ladu_batch.id = kogused.bid 
WHERE 
  t2!='Sildid' 
GROUP BY 
  batch_id

Thats the MySQL code - the problem is following. I need to exclude rows where total is 0 and dat开发者_JAVA百科e_ch older than one month (date_cd format is "2011-01-01 00:00:00"). is it possible? http://jsfiddle.net/tiitremmel/2mvrr/1/ - jsfiddle sample - first table is like mysql result is default and the second table is how the result should look.

Add the date condition into the WHERE-statement and the exclusion of your SUM as a HAVING-statement. It should then look like this:

SELECT    cms_module_ladu_batch.id AS batch_id,
          cms_module_ladu_batch.date_changed AS date_ch,
          kogused.kogus AS kogus,
          SUM(kogused.kogus) AS total 
FROM      cms_module_ladu_batch 
LEFT JOIN kogused ON cms_module_ladu_batch.id = kogused.bid 
WHERE     t2 != 'Sildid' 
          AND (
                  (
                   cms_module_ladu_batch.date_changed < DATE_SUB(NOW(), INTERVAL 1 MONTH)
                   AND SUM(kogused.kogus) > 0
                  )
              OR
                  cms_module_ladu_batch.date_changed > DATE_SUB(NOW(), INTERVAL 1 MONTH)
              )
GROUP BY  batch_id;

Alternatively... use a UNION-Statement to glue two SELECTS together.

SELECT   cms_module_ladu_batch.id AS batch_id,
         cms_module_ladu_batch.date_changed AS date_ch,
         kogused.kogus AS kogus,
         SUM(kogused.kogus) AS total 
FROM     cms_module_ladu_batch 
LEFT JOIN kogused ON cms_module_ladu_batch.id = kogused.bid 
WHERE t2 != 'Sildid' 
         AND cms_module_ladu_batch.date_changed < DATE_SUB(NOW(), INTERVAL 1 MONTH)
GROUP BY batch_id 
HAVING total > 0
UNION
SELECT   cms_module_ladu_batch.id AS batch_id,
         cms_module_ladu_batch.date_changed AS date_ch,
         kogused.kogus AS kogus,
         SUM(kogused.kogus) AS total 
FROM     cms_module_ladu_batch 
LEFT JOIN kogused ON cms_module_ladu_batch.id = kogused.bid 
WHERE t2 != 'Sildid' 
         AND cms_module_ladu_batch.date_changed > DATE_SUB(NOW(), INTERVAL 1 MONTH)
GROUP BY batch_id; 

Try both, see what fits best for your solution.

SELECT 
  cms_module_ladu_batch.id AS batch_id, 
  name, t1, cms_module_ladu_batch.date_changed AS date_ch, 
  t13, t2, t3, t4, bkuup, date_modified, date_created, 
  kogused.aadress AS aadress, 
  kogused.kogus AS kogus, 
  SUM(kogused.kogus) AS total 
FROM 
  cms_module_ladu_batch 
  LEFT JOIN kogused 
    ON cms_module_ladu_batch.id = kogused.bid 
WHERE 
  t2!='Sildid' AND
  date_ch < date_sub(now(), interval 1 month)
GROUP BY 
  batch_id
HAVING 
  SUM(kogused.kogus) <> 0

To filter outside the GROUP BY clause you can use the HAVING keyword.

http://dev.mysql.com/doc/refman/5.5/en/select.html

    SELECT    cms_module_ladu_batch.id AS batch_id,
          cms_module_ladu_batch.date_changed AS date_ch,
          kogused.kogus AS kogus,
          SUM(kogused.kogus) AS total 
FROM      cms_module_ladu_batch 
LEFT JOIN kogused ON cms_module_ladu_batch.id = kogused.bid 
WHERE     t2 != 'Sildid' 
GROUP BY  batch_id
HAVING    (
       (
          cms_module_ladu_batch.date_changed < DATE_SUB(NOW(), INTERVAL 1 MONTH)
          AND SUM(kogused.kogus) > 0
       )
    OR
           cms_module_ladu_batch.date_changed > DATE_SUB(NOW(), INTERVAL 1 MONTH)
           );

This were the final code that worked for me. Great thanks to all, but greatest to Bjoern - your idea of code helped me a lot!

Just add at the bottom:

HAVING (total<>0) AND (date_cd>=ADDDATE(CURDATE(), INTERVAL -1 MONTH))

Other reply (see comments):

HAVING (total<>0) OR (date_cd>=ADDDATE(CURDATE(), INTERVAL -1 MONTH))