perkes456 perkes456 - 5 months ago 11
SQL Question

Sum function in mysql with inner query

I have a query like following:

select b.*, SUM(h.points) as points
from brands b left join
histories h
on b.id = h.brandid and h.userId = $userId
where b.id=$brandId and b.active=1
group by b.id
limit 1


I want to sum only points for following cases:

where h.type != 10, h.points !=0 and points should be between two dates like this: h.time between '2016-06-01' and '2016-06-31'...


I'm thinking this could be done by using a sub-query but I'm not sure how... Can you help me out guys???

The desired output would be:

userId brandId points

1 64 155

1 15 100


And so on that I have each summed points nicely for each user that has scored those points in between two dates and that the type of points is not != 10;

The table structure is like following:

Brands => Histories <= Users

vkp vkp
Answer
select b.id, 
sum(case when h.type <> 10 and h.time between '2016-06-01' and '2016-06-31' then h.points 
    else 0 end) as points
from brands b 
left join histories h on b.id = h.brandid and h.userId = $userId
and b.active=1 and b.id=$brandId
group by b.id
Comments