Qualify and group by statement conflict

Analytics

Qualify and group by statement conflict

/*

Hi,  I am trying to run the below query, in vain. I was able to get the results by using a different logic.

However I am not able to understand why my query for search didn’t work, while my query for sales worked (they are both essentially the same query with different column names)

*/

— Search Query. This query didn’t work, the error is selected non – aggregate values must be a part of associated group, but when I add one more group by (which is not required) the error is exact opposite.

SELECT
(CASE WHEN a.DATE BETWEEN '2013-10-31' AND '2013-11-27' THEN 'Phase1'
WHEN a.DATE BETWEEN '2013-11-28' AND '2013-12-15' THEN 'Phase2'
WHEN a.DATE BETWEEN '2013-12-16' AND '2013-12-31' THEN 'Phase3'
WHEN a.DATE BETWEEN '2014-01-01' AND '2014-01-31' THEN 'Phase4' END) AS Phases,
c.categoryname,
b.keyword,
SUM(a.COUNT) AS kw_count,
RANK() OVER(PARTITION BY phases ORDER BY kw_count DESC) AS ranking
FROM
kw_id_table AS a
LEFT JOIN kw_id_lkp_table AS b
ON a.kw_id = b.kw_id AND a.DATE BETWEEN '2013-10-31' AND '2014-01-31'
LEFT JOIN kw_category_map_table AS M
ON a.kw_id = m.kw_id
INNER JOIN Category_name_table c
ON m.categ_id = c.categ_id
WHERE
a.DATE >= '2013-10-31' AND a.DATE <= '2014-01-31'
GROUP BY 1,2,3
QUALIFY ranking < 201;

—This query works! Essentially the same query as above, please help me figure out whats wrong with the above query.

SELECT
(CASE WHEN a.DATE BETWEEN '2013-10-31' AND '2013-11-27' THEN 'Phase1'
WHEN a.DATE BETWEEN '2013-11-28' AND '2013-12-15' THEN 'Phase2'
WHEN a.DATE BETWEEN '2013-12-16' AND '2013-12-31' THEN 'Phase3'
WHEN a.DATE BETWEEN '2014-01-01' AND '2014-01-31' THEN 'Phase4'
END) AS Phases,
c.categoryname,
SUM(sales) AS sales,
RANK() OVER(PARTITION BY phases ORDER BY sales DESC) AS ranking
FROM
sales_table AS ck
LEFT JOIN category_name_table AS b
ON ck.categ_id = b.categ_id AND ck.sales_date >= '2013-08-01' --PI
WHERE
ck.trans_date >= '2013-10-31' AND ck.trans_date <= '2014-01-31'
GROUP BY 1,2
QUALIFY ranking < 201;

/* Few folks around here asked me to re run the codes by separating group by and qualify statements (i was able to do that) but I am confused as to why 1 would work and other wouldnt

also many asked to revalidate my results from the sales code, i did that too, they were same */

2 REPLIES
Highlighted
Junior Contributor

Re: Qualify and group by statement conflict

Both queries are not your actual queries (using keywords DATE and COUNT as column names), so it's hard to tell why it's failing.

The common coding error:

When OALP and GROUP BY is mixed within the same query, you can use only columns in GROUP BY or columns with aggregate functions in OLAP as OLAP functions are processed after GROUP BY/HAVING.

In your case the 2nd query will fail due to renaming "SUM(sales)" to "sales" and then using "sales" in "PARTITION BY":

The parser willl not use the alias but the original column, to fix it use another alias or "PARTITION BY SUM(sales)".

Re: Qualify and group by statement conflict

SELECT

   (CASE WHEN a.DATE BETWEEN '2013-10-31' AND '2013-11-27' THEN 'Phase1'

         WHEN a.DATE BETWEEN '2013-11-28' AND '2013-12-15' THEN 'Phase2'

         WHEN a.DATE BETWEEN '2013-12-16' AND '2013-12-31' THEN 'Phase3'

         WHEN a.DATE BETWEEN '2014-01-01' AND '2014-01-31' THEN 'Phase4'

    END) AS Phases,

   c.categoryname,

   SUM(sales) OVER (PARTITION BY Phases),

   RANK() OVER(PARTITION BY phases ORDER BY sales DESC) AS ranking

FROM

   sales_table AS ck

LEFT JOIN category_name_table AS b

  ON ck.categ_id = b.categ_id AND ck.sales_date >= '2013-08-01' --PI

WHERE

   ck.trans_date >= '2013-10-31' AND ck.trans_date <= '2014-01-31'

GROUP BY 1,2

QUALIFY ranking < 201;


An SQL query that contains both ordered analytical functions and aggregate functions can have both a QUALIFY clause and a HAVING clause, as in the following example:

   SELECT StoreID, SUM(sale),

   SUM(profit) OVER (PARTITION BY StoreID)

   FROM facts

   GROUP BY StoreID, sale, profit

   HAVING SUM(sale) > 15

   QUALIFY SUM(profit) OVER (PARTITION BY StoreID) > 2;

http://www.info.teradata.com/htmlpubs/DB_TTU_14_10/index.html#page/SQL_Reference/B035_1145_112A/Orde...

Check the above Query , this should work