Selecting records based on count

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server Newbies » Selecting records based on count

Selecting records based on count

Rate Topic

Display Mode

Topic Options

Author

Message

Candls

Posted Monday, August 27, 2012 3:44 PM

Forum Newbie

Group: General Forum Members
Last Login: Friday, September 07, 2012 2:26 PM
Points: 8, Visits: 30

I've joined several tables in order to get data I need. However as I look @ data I realized there's a one to many relationship between product id and regions.

So, now I am attempting to filter records based on whether or not there's one or less than one regions associated to product that fall within specified status conditions (noted below).. I need help in forming proper syntax for accomplishing this task.

My query looks something like this...

this assumes only one table is being used but there's total of three..

select product, region, status
from products prd
inner join pd_status S on s.product_status_key=prd.status_key
where prd..product_status in ('active', 'on hold')
and sa.sa_status in ('canceled', 'On Hold')
and count(sa_status)<2.

I keep getting msg 147. I've tried with having, and as and contained in select as 'if' statement but still no luck. Any help is greatly appreciated.

Post #1350628

CapnHector

Posted Monday, August 27, 2012 4:03 PM

SSC Eights!

Group: General Forum Members
Last Login: Friday, May 17, 2013 10:07 AM
Points: 935, Visits: 1,709

Take a look at HAVING. from your description i think that is what you are going to want.

http://msdn.microsoft.com/en-us/library/ms180199.aspx

For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw

Need to Split some strings? Jeff Moden's DelimitedSplit8K
Jeff Moden's Cross tab and Pivots Part 1
Jeff Moden's Cross tab and Pivots Part 2

Jeremy Oursler

Post #1350633

lucas_leon88

Posted Monday, August 27, 2012 4:04 PM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, April 18, 2013 6:00 AM
Points: 3, Visits: 32

did you try something as
declare @region_assoc_prod int
set @region_assoc_prod = (select count(sa_status) from products)
select product, region, status
from products prd
inner join pd_status S on s.product_status_key=prd.status_key
where prd..product_status in ('active', 'on hold')
and sa.sa_status in ('canceled', 'On Hold')
and @region_assoc_prod<2.

or similar query?

Yes, read about having clause

Post #1350634

Candls

Posted Tuesday, August 28, 2012 7:28 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, September 07, 2012 2:26 PM
Points: 8, Visits: 30

This worked. Thank YOu.

Post #1350917

Candls

Posted Tuesday, August 28, 2012 7:59 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, September 07, 2012 2:26 PM
Points: 8, Visits: 30

Thank you. Very Helpful.

Post #1350959

Luis Cazares

Posted Tuesday, August 28, 2012 8:00 AM

SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 5:52 PM
Points: 960, Visits: 1,921

What worked? Lucas option doesn't seem like a great option.
You could use something like this for the HAVING

SELECT product, region, status 
FROM products prd 
INNER JOIN pd_status S on s.product_status_key=prd.status_key
WHERE prd.product_status in ('active', 'on hold')
AND s.sa_status in ('canceled', 'On Hold')
GROUP BY product, region, status 
 HAVING count(sa_status)<2.

However, I'm not sure it can work. You didn't post your DDL so we don't know if region is a column from Product or Pd_Status. It could have helped if you use complete names of the fields like you did on the where clauses.
For even better help, you could have posted sample data. That way we can have a better idea of what's needed. You might even need a LEFT JOIN or no JOIN at all, but is hard for us to know if we don't see what you see.

Luis C.
Please don't trust me, test the solutions I give you before using them.
Forum Etiquette: How to post data/code on a forum to get the best help

Post #1350962

Candls

Posted Tuesday, August 28, 2012 8:11 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, September 07, 2012 2:26 PM
Points: 8, Visits: 30

I tried declare and having clause both worked. My syntax for having which I tried yesterday was incorrect.
I am now verifying data results to ensure I am fetching products with only 1 region so I can now use query to build a report for my business partner. Smile

Much appreciate the feedback. It's been years since I've played in data and code...am refreshing my skills again BigGrin

Post #1350971

« Prev Topic | Next Topic »

Permissions