How do I use a WHERE clause in this query?

How do I use a WHERE clause in this query?
I wish to use a where on the COUNT column
Thanks


select b.item_id as "INV_MAST_item_id",a.alternate_code as "ALTERNALTE_CODE_alternate_code",
         ROW_NUMBER() OVER ( PARTITION BY a.inv_mast_uid
                             ORDER BY a.inv_mast_uid DESC
                           ) rownum,
            COUNT(*) OVER (PARTITION BY a.alternate_code) counts , a.delete_flag
from alternate_code a
inner join inv_mast b
on b.inv_mast_uid = a.inv_mast_uid

Open in new window

Richard GouetteIT ManagerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

_agx_Commented:
Wrap it in a WITH clause. Then you can use the aggregate in the outer WHERE clause

Replace "WHERE  counts = @something" with whatever operation you want to perform on the "counts" column.

;WITH data
AS
(
select b.item_id as "INV_MAST_item_id",a.alternate_code as "ALTERNALTE_CODE_alternate_code",
         ROW_NUMBER() OVER ( PARTITION BY a.inv_mast_uid
                             ORDER BY a.inv_mast_uid DESC
                           ) rownum,
            COUNT(*) OVER (PARTITION BY a.alternate_code) counts , a.delete_flag
from alternate_code a
inner join inv_mast b
on b.inv_mast_uid = a.inv_mast_uid
)
SELECT *
FROM    data
WHERE  counts = @something

Open in new window

0
Richard GouetteIT ManagerAuthor Commented:
I get:

Invalid column name 'counts'.

Open in new window

It doesn;t like me tryign to use that as a column, clearly..
;(
0
_agx_Commented:
Which dbms?  Works fine with SQL Server. Note, I fudged the data types for demo only

http://sqlfiddle.com/#!18/1ecb1/2/0
0
Learn SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Richard GouetteIT ManagerAuthor Commented:
Microsoft SQL Server 2008 (SP1) - 10.0.2573.0 (X64)   Feb  4 2011 11:27:06   Copyright (c) 1988-2008 Microsoft Corporation  Standard Edition (64-bit) on Windows NT 6.0 <X64> (Build 6002: Service Pack 2) (VM)

hmm strange

I tried:

from alternate_code a
inner join inv_mast b
on b.inv_mast_uid = a.inv_mast_uid
WHERE counts = 1

Open in new window

0
_agx_Commented:
The "counts" column can't be used in a WHERE clause of the original query because it doesn't exist yet.  You need to wrap it in a WITH clause, like in my example:

http://sqlfiddle.com/#!18/1ecb1/2/0
0
PortletPaulEE Topic AdvisorCommented:
You can use WITH.... but you may also the more traditional approach (a simple "subquery").
select
*
from (
      SELECT
            b.item_id AS "INV_MAST_item_id"
          , a.alternate_code AS "ALTERNALTE_CODE_alternate_code"
          , ROW_NUMBER() OVER (PARTITION BY a.inv_mast_uid
                                ORDER BY a.inv_mast_uid DESC
                                ) rownum
          , COUNT(*) OVER (PARTITION BY a.alternate_code) counts
          , a.delete_flag
      FROM alternate_code a
      INNER JOIN inv_mast b ON b.inv_mast_uid = a.inv_mast_uid
      ) d
where counts > 1
;

Open in new window

What you CANNOT do, when you use OVER(...), is to refer to that result by a column alias in a single select statement. By using an extra select "layer" the column alias will be recognized in that subsequent where clause.

{+edits}
Column aliases NOT working in a where clause can be confusing, but the cause of this is due to the fact that a select clause is performed AFTER the FROM and WHERE clauses. So as it gets executed the query looks more like this:

FROM alternate_code a INNER JOIN inv_mast b ON b.inv_mast_uid = a.inv_mast_uid
WHERE [counts] > 1 --<< oops! "counts" does not yet exist

SELECT ... COUNT(*) OVER (PARTITION BY a.alternate_code) counts

& that's why the extra "layer" of query allows use of the column alias
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Richard GouetteIT ManagerAuthor Commented:
Excellent.
Many thanks!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.