Complex SQL query with group by and having

Question

Hello All,

I am having a table orders

orders (
id  int unsigned not null,
fcr_date  TIMESTAMP,
completion_date TIMESTAMP,
factory_no  varchar(255),
vendor_no  varchar(255))

Please ignore the data type typos if any.

I want to write a sql query that helps me filter the data per vendor factory. The data to fetch includes the number of orders per vendor factory(a unique group of vendor_no, factory_no), vendor_no, factory_no and the percentage of orders for which fcr_date is greater than completion_date(so percentage = number of orders where fcr_date is greater than completion date / count of orders). After that i need to filter the data where percentage is greater than say 20%.

I wrote the following query:

SELECT  vendor_no As vendor,
        factory_no As factory,
        COUNT(1) as count,
        SUM(CASE WHEN fcr_date > completion_date THEN 1 ELSE 0 END) as filter_orders,
    ROUND(filter_orders / count * 100, 4) as  percent  
FROM    @orders
GROUP BY vendor_no,
        factory_no
HAVING percent>20

but postgresql complains that it needs to have a column called percent in table to filter the results based on that. Any help is appreciated.

Thanks.

Answer 1

Change it to:

HAVING ROUND(filter_orders / count * 100, 4) > 20

Because percent isn't an actual column, you need to give it the calculation to perform the filter.

Edit

OK, looking at this further, you've got at least two ways to write this: the one I'd recommend is the first, which involves wrapping in a sub-query (as someone already suggested):

Option 1

SELECT  vendor As vendor,
     factory As factory,
     [count],
     ROUND(filter_orders / count * 100, 4) as  [percent]
FROM
(
    SELECT  vendor_no As vendor,
      factory_no As factory,
      COUNT(1) as count,
      SUM(CASE WHEN fcr_date > completion_date THEN 1 ELSE 0 END) as filter_orders
    FROM    @orders
    GROUP BY vendor_no,
      factory_no
) AS a
WHERE ROUND(filter_orders / count * 100, 4) > 20

Option 2

SELECT  vendor_no As vendor,
     factory_no As factory,
     COUNT(1) as count,
     SUM(CASE WHEN fcr_date > completion_date THEN 1 ELSE 0 END) as filter_orders,
     ROUND(SUM(CASE WHEN fcr_date > completion_date THEN 1 ELSE 0 END) / count(1) * 100, 4) as  [percent]
FROM    @orders
GROUP BY vendor_no,
     factory_no
HAVING ROUND(SUM(CASE WHEN fcr_date > completion_date THEN 1 ELSE 0 END) / count(1) * 100, 4) > 20

Answer 2

I'm pretty sure you can't use aliases (like percent) in having clauses or group by clauses. And by "pretty" I mean Oracle won't let me use aliases in having/group by clauses...not sure about other vendors.

Answer 3

Wrap your query with an outer filtering query:

SELECT * FROM (
    SELECT  vendor_no As vendor,
            factory_no As factory,
            COUNT(1) as count,
            SUM(CASE WHEN fcr_date > completion_date THEN 1 ELSE 0 END) as filter_orders,
        ROUND(filter_orders / count * 100, 4) as  percent  
    FROM    @orders
    GROUP BY vendor_no,
            factory_no
) x
WHERE percent>20