SQL how to group_by and count

问题: i have the following query SELECT c.`name` as categories,times_booked.booked as booked FROM req_profiles rq...

问题:

i have the following query

 SELECT c.`name` as categories,times_booked.booked as booked
              FROM req_profiles rq                
                                inner join categories as c on c.id = rq.category_id
              left join 
                (
                SELECT rq.id, COUNT(rq.id) as booked
                FROM req_profiles as rq
                inner join profile_matchings as pm on pm.req_profile_id = rq.id
                WHERE pm.`status` =  'booked'
                AND rq.user_id = 736
                AND (pm.created_at BETWEEN '2018-01-01 00:00' AND '2019-02-13 00:00')
                GROUP BY rq.id
                ) as times_booked on times_booked.id = rq.id

              where rq.user_id=736
                                and rq.`status` = 'active'

              ORDER BY times_booked.booked,rq.id desc           
                                limit 5

this is what i get:

categories| booked
-------------------
  Talent      NULL
  Talent       1
  Talent       1

but i would like to get the result something like this:

categories| booked
-------------------
  Talent      2

Thanks for the help!


回答1:

use your query as a subquery anf group by

select categories, count(booked)
from (
     SELECT c.`name` as categories,times_booked.booked as booked
                  FROM req_profiles rq                
                                    inner join categories as c on c.id = rq.category_id
                  left join 
                    (
                    SELECT rq.id, COUNT(rq.id) as booked
                    FROM req_profiles as rq
                    inner join profile_matchings as pm on pm.req_profile_id = rq.id
                    WHERE pm.`status` =  'booked'
                    AND rq.user_id = 736
                    AND (pm.created_at BETWEEN '2018-01-01 00:00' AND '2019-02-13 00:00')
                    GROUP BY rq.id
                    ) as times_booked on times_booked.id = rq.id

                  where rq.user_id=736
                                    and rq.`status` = 'active'

                  ORDER BY times_booked.booked,rq.id desc           
                                    limit 5
    ) t 
    group by categories 

回答2:

select x.a, count(x.b) 
from (
select 'a' a, 10 b from dual
union all
select 'a' a, 20 from dual
union all 
select 'b' a, 10 from dual 
)x

Hope it helps!

  • 发表于 2019-02-20 00:14
  • 阅读 ( 174 )
  • 分类:sof

条评论

请先 登录 后评论
不写代码的码农
小编

篇文章

作家榜 »

  1. 小编 文章
返回顶部
部分文章转自于网络,若有侵权请联系我们删除