How to use CASE and HAVING clauses efficiently to optimize SQL Server Query

Question

I have a SQL Query i.e.

SELECT 
    A2P.aid,
    COUNT(*) [PaperCount],
    [2010] = SUM(CASE WHEN A2P.p_year = 2010 THEN 1 ELSE 0 END),
    [2011] = SUM(CASE WHEN A2P.p_year = 2011 THEN 1 ELSE 0 END),
    [2012] = SUM(CASE WHEN A2P.p_year = 2012 THEN 1 ELSE 0 END),
    [2013] = SUM(CASE WHEN A2P.p_year = 2013 THEN 1 ELSE 0 END),
    [2014] = SUM(CASE WHEN A2P.p_year = 2014 THEN 1 ELSE 0 END) 
FROM 
    sub_aminer_author2paper A2P
WHERE 
    aid IN (SELECT 
                aid 
            FROM 
                sub_aminer_author 
            WHERE 
                paper_count >= 20
           )
AND A2P.p_year BETWEEN 2010 AND 2014
GROUP BY 
    A2P.aid
HAVING 
    COUNT(DISTINCT A2P.pid) >= 2
ORDER BY 
    A2P.aid

Now, the issue is I want to have rows in output containing value >= 2 in each column i.e. 2010, 2011, 2012, 2013, and 2014 OR the values in column Paper_Count >= 10 for each row in output.

Moreover, I have used the condition as:

HAVING 
        COUNT(DISTINCT A2P.pid) >= 2

But results are not as desired. See the image for output...

As it is clear that 0 values are also retrieved in output which is not desired.

Please help in this regard.
Thanks!

"optimize" and "efficiently" don't seem to relate to your problem. Maybe edit the title. — JimmyB, Oct 29 '15 at 08:51

score 1 · Answer 1 · answered Sep 07 '19 at 12:20

1

I have compiled from different sources and optimized my query if someone wishes to see how I achieved it. Please have a look at this. https://junaidtechblog.wordpress.com/2019/09/04/optimize-sql-query-groupby-having/

answered Sep 07 '19 at 12:20

Junaid Ali

361
2
9

Julien Vavasseur · Accepted Answer · 2015-10-29T08:49:05.693

Query your Select for your conditions:

Select A2P.aid, [PaperCount], [2010, [2011], [2012], [2013], [2014]
From (
    SELECT 
        A2P.aid,
        COUNT(*) [PaperCount],
        [2010] = SUM(CASE WHEN A2P.p_year = 2010 THEN 1 ELSE 0 END),
        [2011] = SUM(CASE WHEN A2P.p_year = 2011 THEN 1 ELSE 0 END),
        [2012] = SUM(CASE WHEN A2P.p_year = 2012 THEN 1 ELSE 0 END),
        [2013] = SUM(CASE WHEN A2P.p_year = 2013 THEN 1 ELSE 0 END),
        [2014] = SUM(CASE WHEN A2P.p_year = 2014 THEN 1 ELSE 0 END) 
    FROM 
        sub_aminer_author2paper A2P
    WHERE 
        aid IN (SELECT aid FROM sub_aminer_author WHERE paper_count >= 20)
    AND A2P.p_year BETWEEN 2010 AND 2014
    GROUP BY 
        A2P.aid
) x
Where [2010] >= 2 and [2011] >= 2 and [2012] >= 2 and [2013] >= 2 and [2014] >= 2;

Or something like this:

Select aid
    , [PaperCount] = [2010] + [2011] + [2012] + [2013] + [2014]
    , [2010], [2011], [2012], [2013], [2014]
From (
    SELECT aid, p_year
    FROM @sub_aminer_author2paper A2P
    WHERE aid IN (SELECT aid FROM @sub_aminer_author WHERE paper_count >= 20)
        AND p_year BETWEEN 2010 AND 2014
    GROUP BY A2P.aid, A2P.p_year
)p
Pivot (
    count(p_year)
    For p_year In ([2010], [2011], [2012], [2013], [2014])
) as piv
Where [2010] >= 2 and [2011] >= 2 and [2012] >= 2 and [2013] >= 2 and [2014] >= 2;

How to use CASE and HAVING clauses efficiently to optimize SQL Server Query

2 Answers2