January 28, 2017 at 12:44 am
I am getting error as column rx.postive does not exist in below query.Kindly help.
select account_id,product_id,sum(rx.postive),sum(rx.negative)
from(select
account_id,product_id,
case when rx_formula in ('approved','unrestricted') then '1' else'0' end as positive,
case when rx_formula not in ('approved','unrestricted') then '1' else'0' end as negative
from status)rx
group by 1,2
Saravanan
January 28, 2017 at 1:40 am
You're missing an "i" in rx.positive.
Also, your CASE statements should be returning 0 or 1 as integers (without the single quotes, which is identifying them as strings and will give an error that the SUM function is incompatible with the varchar data type).
Your query will also give the error "Each GROUP BY expression must contain at least one column that is not an outer reference." - this is because you are using column ordinals in your GROUP BY clause "group by 1, 2" - please avoid doing that; it makes it harder to read and gives room for error if you later change the order of the columns in your SELECT clause. It has also caused this particular error. Changing the GROUP BY clause to list column names, e.g. "group by rx.account_id, rx_product_id" would resolve this error and make for more reliable code going forward.
As a suggestion, you could also remove the need for a subquery by moving those CASE statements inside the SUM statements, as below.
select
status.account_id,
status.product_id,
sum(case when status.rx_formula in ('approved','unrestricted') then 1 else 0 end) as positive,
sum(case when status.rx_formula not in ('approved','unrestricted') then 1 else 0 end) as negative
from status
group by status.account_id,
product_id
Andrew P.
January 28, 2017 at 6:30 am
Andrew P - Saturday, January 28, 2017 1:40 AMYou're missing an "i" in rx.positive.Also, your CASE statements should be returning 0 or 1 as integers (without the single quotes, which is identifying them as strings and will give an error that the SUM function is incompatible with the varchar data type).
Your query will also give the error "Each GROUP BY expression must contain at least one column that is not an outer reference." - this is because you are using column ordinals in your GROUP BY clause "group by 1, 2" - please avoid doing that; it makes it harder to read and gives room for error if you later change the order of the columns in your SELECT clause. It has also caused this particular error. Changing the GROUP BY clause to list column names, e.g. "group by rx.account_id, rx_product_id" would resolve this error and make for more reliable code going forward.
As a suggestion, you could also remove the need for a subquery by moving those CASE statements inside the SUM statements, as below.
select
status.account_id,
status.product_id,
sum(case when status.rx_formula in ('approved','unrestricted') then 1 else 0 end) as positive,
sum(case when status.rx_formula not in ('approved','unrestricted') then 1 else 0 end) as negative
from status
group by status.account_id,
product_id
Andrew P.
Thanks Andrew for quick and accurate response.
Saravanan
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply