mysql groupby and having


SELECT words, COUNT(*) AS cnt
FROM words
GROUP BY word
HAVING COUNT(*) > 1
by doctrine:
$qb = $this->getEntityManager()->createQueryBuilder();
$qb
    ->select(array(
        'u.id', 
        'u.name', 
        'u.reseller_code', 
        'u.first_name',
        'u.last_name',
        'u.email',
        'u.country',
        'COUNT(a) as numam', 
        'SUM(IF(a.activity_year = :year, a.activity_value, 0)) as sumay', 
        'SUM(IF(a.activity_year = :year AND a.activity_month = :month, a.activity_value, 0)) as sumam'
        ))
    ->from('MyBundle:User', 'u')
    ->leftJoin('u.activities', 'a')
    ->groupBy('u.id')
    ->orderBy('u.name', 'ASC')
    ->setParameter('month', $month)
    ->setParameter('year', $year)

评论

热门帖子