Query with multiple data filters 1

[dstrange]

Hi,
I need help with syntax but I dunno if what I want is possible. I need to retrieve the # of items sold and then broken down into; year to date, period to date , and total items sold. Can this be done with 1 query?

SELECT count(id) as 'Total Items Sold',
       if((date_format(visit_date,'%Y') = date_format(current_date,'%Y')), count(id), 0) AS `Year To Date Items Sold`,
       if((date_format(visit_date,'%Y-%m-%d') BETWEEN date_format('2007-05-06','%Y-%m-%d') AND date_format('2007-11-26','%Y-%m-%d')), 0, count(id)) AS `Period To Date Items Sold`
FROM table
GROUP BY id;

 

[r937]

yes, it's possible

start by ...
1) removing the GROUP BY id
2) replacing IF with CASE



r937.com | rudy.ca

 

[dstrange]

Any help with the CASE?

 

[r937]

select count(*)                                as total_sold
     , sum(
       case when visit_date 
               > date_sub(current_date
                        , interval dayofyear(current_date) day )
             and visit_date 
              <= date_add(
                 date_sub(current_date
                        , interval dayofyear(current_date) day )
                        , interval 1 year )
            then 1 else 0 end )                as YTD_sold
     , sum(
       case when visit_date
                 between '2007-05-06'
                     and '2007-11-26'
            then 1 else 0 end )                as PTD_sold
  from daTable

r937.com | rudy.ca