You can use conditional sum for this calculation something as
select
date_format(created,'%d-%m') as created_date ,
sum( case when status='open' then 1 else 0 end )+
sum( case when status='new' then 1 else 0 end )+
sum( case when status='resolved' and date(resolved) > date(created) then 1 else 0 end ) as open
from table_name
where
YEAR(Created) = '2015'
group by created_date ;
Here is a test case
mysql> select * from test ;
+------+---------------------+---------------------+----------+
| id | created | resolved | status |
+------+---------------------+---------------------+----------+
| 1 | 2015-05-10 00:00:00 | 1970-01-01 00:00:00 | open |
| 2 | 2015-05-10 00:00:00 | 1970-01-01 00:00:00 | new |
| 3 | 2015-05-10 00:00:00 | 2015-05-12 00:00:00 | resolved |
| 4 | 2015-05-11 00:00:00 | 1970-01-01 00:00:00 | open |
| 5 | 2015-05-11 00:00:00 | 1970-01-01 00:00:00 | new |
| 6 | 2015-05-11 00:00:00 | 2015-05-11 00:00:00 | resolved |
+------+---------------------+---------------------+----------+
6 rows in set (0.00 sec)
mysql> select
-> date_format(created,'%d-%m') as created_date ,
-> sum( case when status='open' then 1 else 0 end )+
-> sum( case when status='new' then 1 else 0 end )+
-> sum( case when status='resolved' and date(resolved) > date(created) then 1 else 0 end ) as open
-> from test
-> where
-> YEAR(Created) = '2015'
-> group by created_date ;
+--------------+------+
| created_date | open |
+--------------+------+
| 10-05 | 3 |
| 11-05 | 2 |
+--------------+------+
2 rows in set (0.00 sec)
UPDATE
The count of open is a incremental as per the comment. So the following should do the trick
select
created_date,
@open:= @open+open_ticket-resolved as open
from(
select
date_format(created,'%d-%m') as created_date ,
sum( case when status='open' then 1 else 0 end )+
sum( case when status='new' then 1 else 0 end )+
sum( case when status='resolved' and ( date(resolved) > date(created) or date(resolved) = date(created)) then 1 else 0 end ) as open_ticket,
sum(case when status = 'resolved' and date(resolved) = date(created) then 1 else 0 end) as resolved
from test
where
YEAR(Created) = '2015'
group by created_date
)x,(select @open:=0)r order by created_date ;
From the above sample data it will have
+--------------+------+
| created_date | open |
+--------------+------+
| 10-05 | 3 |
| 11-05 | 5 |
+--------------+------+
UPDATE:
Count the previous tickets which got resolved on a given date and deduct that from the current day open ticket, for that you need a left join
something as
select
created_date,
@open:= @open+open_ticket-resolved_ticket as open
from(
select
date_format(t.created,'%d-%m') as created_date ,
sum( case when t.status='open' then 1 else 0 end )+
sum( case when t.status='new' then 1 else 0 end )+
sum( case when t.status='resolved' and ( date(t.resolved) > date(created) or date(t.resolved) = date(created)) then 1 else 0 end ) as open_ticket,
coalesce(tot,0) as resolved_ticket
from test t left join(
select count(*) as tot, date(resolved) as resolved from test where status='resolved' group by date(resolved)
)x on date(x.resolved) = date(t.created)
where
YEAR(t.created) = '2015'
group by created_date
)x,(select @open:=0)r order by created_date ;
OUTPUT :
+--------------+------+
| created_date | open |
+--------------+------+
| 10-05 | 3 |
| 11-05 | 5 |
| 12-05 | 7 |
+--------------+------+
If it is safe to assume that a single order can have only one distinct StoreID
, you could resolve your issue by generating the set of distinct OrderID, StoreID
pairs from orderLines
and join that set instead of the table itself. That way you will not need to use DISTINCT with aggregation:
SELECT
`ol`.`storeID`,
SUM(`o`.`grossValue`) AS 'Total',
SUM(`o`.`paymentValue`) AS 'paymentTotal'
FROM
`orders` AS `o`
LEFT JOIN
(SELECT DISTINCT `OrderID`, `StoreID` FROM `orderLines`) AS `ol`
ON `o`.`orderID` = `ol`.`orderID`
WHERE
(`o`.`orderDate` BETWEEN '2015-07-07 00:00:00' AND '2015-07-07 23:59:59')
GROUP
BY `ol`.`storeID`
;
And, as has already been mentioned, you should probably use INNER JOIN instead of the LEFT JOIN, unless you have orders that do not have order lines but for some reason do have a gross value and a payment (a little strange, but may be there is a reason for that), and you want them included in the results. (They will be represented as a single row with a null Store ID.)
Best Answer