Newbie Frustration - SQL Server 2000

Question

Post reply

Newbie Frustration - SQL Server 2000

cintijohn

SSC Enthusiast

Points: 116
More actions
November 25, 2009 at 7:57 am

#217277

Currently have syntax written to get at generating a percentage but I need to go a step further with the ability to condition a result set by a specific percentage. Ex., I need to filter a result set on 50% or more of the population that said "definitely buy/possibly buy" product X. I'm trying to do this either on-the-fly or a temp table.
So my question is how can I get at that >= 50% based on my current syntax (if possible)...
select product_id as Product_ID,
round(cast(sum(case when purchase_id in (4,5) then 1 else 0 end) as float)/
cast(sum(case when case_id > 0 then 1 else 0 end) as float) * 100,1) as [Purchase_%]
from ratings
group by product_id

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

Bru Medishetty SSC-Insane Points: 23606 More actions · Answer 1

Use having clause at the end.

having [Purchase_%] > 50

I did not try on your query since I don't have the table / column details.

Bru Medishetty

Blog -- LearnSQLWithBru

Join on Facebook Page Facebook.comLearnSQLWithBru

Twitter -- BruMedishetty

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

Bru hinted at a very important thing here at SSC. We can offer suggestions for your query, but it is very difficult to provide accurate solutions without having the table structure and sample data with example of what the desired results should look like.

If you could provide more information, we would be able to better serve you.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Jeff Moden SSC Guru Points: 1004372 More actions · Answer 3

CirquedeSQLeil (11/25/2009)
Bru hinted at a very important thing here at SSC. We can offer suggestions for your query, but it is very difficult to provide accurate solutions without having the table structure and sample data with example of what the desired results should look like.
If you could provide more information, we would be able to better serve you.

Correct... please see the article in Jason's signature line titled " Posting Data Etiquette - Jeff Moden " for how to easily do that. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004372 More actions · Answer 4

Bru Medishetty (11/25/2009)
Use having clause at the end.
having [Purchase_%] > 50
I did not try on your query since I don't have the table / column details.

Bru... if you copy and paste code from QA or SSMS instead of typing it directly, you won't get the & g t ; stuff...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Bru Medishetty SSC-Insane Points: 23606 More actions · Answer 5

Jeff Moden (11/25/2009)
Bru Medishetty (11/25/2009)
Use having clause at the end.
having [Purchase_%] > 50
I did not try on your query since I don't have the table / column details.
Bru... if you copy and paste code from QA or SSMS instead of typing it directly, you won't get the & g t ; stuff...

Thanks Jeff..

This is one of those examples, I was referring earlier today in suggestions thread.

In fact I have not noticed that g t ; stuff until you mentioned. I usually copy paste from the SSMS or QA, but had not done here.

Bru Medishetty

Blog -- LearnSQLWithBru

Join on Facebook Page Facebook.comLearnSQLWithBru

Twitter -- BruMedishetty

Jeff Moden SSC Guru Points: 1004372 More actions · Answer 6

They finally have the code windows so close to being right that I don't want them to touch them for fear of them mucking them up like they have in the past. They were absolutely terrible about a year ago. I actually avoided posting code because they were so bad.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

cintijohn SSC Enthusiast Points: 116 More actions · Answer 7

Hi Bru:

I'd tried earlier with the HAVING syntax but that didn't work since I believe everything is done on-the-fly (?). Basically I need to take a value that is created on-the-fly and go one step further by filtering/conditioning on that value.

Table structure is...

product_id = int

purchase_id = tinyint

case_id = int

Bru Medishetty SSC-Insane Points: 23606 More actions · Answer 8

Have you seen what Jason and I replied. There is not enough information that can be used.

What do you mean on the fly? Is there a variable that needs to be used? If so that details is needed

Table structure and some sample script will make answering quicker.

Look the first link Jeff Moden's signature. Post the reply accordingly...

Bru Medishetty

Blog -- LearnSQLWithBru

Join on Facebook Page Facebook.comLearnSQLWithBru

Twitter -- BruMedishetty

cintijohn SSC Enthusiast Points: 116 More actions · Answer 9

Bru:

Thanks for your help, I'm definitely feeling my way through this. What I'm looking for is establishing a temporary table (?) to hold the purchase_% output and then filter/condition on that temporary table.

This is one of those situations where if you were sitting next to me it would all make better sense.

Thanks again!

Bru Medishetty SSC-Insane Points: 23606 More actions · Answer 10

I understand.

There are more difficult questions than yours, that have been solved by posting some technical details.

I feel you need to use a local variable (something like this)

DECLARE @PCTVALUE INT

SET @PCTVALUE = '50'

/* This can be hard coded value or you can even

set the value using a query (If that is what you meant by on the fly) */

All I would suggest (one more time) is to give more technical details how you want that % value to be retrieved, who enters that are how is it determined?

Bru Medishetty

Blog -- LearnSQLWithBru

Join on Facebook Page Facebook.comLearnSQLWithBru

Twitter -- BruMedishetty

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 11

How about something more like this:

select

product_id as Product_ID,

round(cast(sum(case when purchase_id in (4,5) then 1 else 0 end) as float)/

cast(sum(case when case_id > 0 then 1 else 0 end) as float) * 100,1) as [Purchase_%]

from

dbo.ratings

group by

product_id

having

round(cast(sum(case when purchase_id in (4,5) then 1 else 0 end) as float)/

cast(sum(case when case_id > 0 then 1 else 0 end) as float) * 100,1) >= 50.0

HVT Valued Member Points: 67 More actions · Answer 12

Hello Bru,

I'm just chiming in here but you really do need to add more info for this. However, based off what you said, you should do just just that.

Create a temp table with product_id as a column and place a case statement to do what you want and alias it as [Purchase%] for the second column. Then create joins on product_id and [purchase_%] from the ratings and temp table.

I would have actually tried this before suggesting it but I don't have enough data to recreate what you're doing.

Regards,

Jatinear

Bru Medishetty SSC-Insane Points: 23606 More actions · Answer 13

Jatinear (11/30/2009)
Hello Bru,
I'm just chiming in here but you really do need to add more info for this. However, based off what you said, you should do just just that.
Create a temp table with product_id as a column and place a case statement to do what you want and alias it as [Purchase%] for the second column. Then create joins on product_id and [purchase_%] from the ratings and temp table.
I would have actually tried this before suggesting it but I don't have enough data to recreate what you're doing.
Regards,
Jatinear

Hi Jatinear,

Do you know what you are replying to?

I am not the one who has the issue, read the thread completely from the beginning. It was my suggestion, to the OP and I am replying to the OP with regard to previous replies.

Hope you are clear with this. 🙂 🙂 😉

Bru Medishetty

Blog -- LearnSQLWithBru

Join on Facebook Page Facebook.comLearnSQLWithBru

Twitter -- BruMedishetty

HVT Valued Member Points: 67 More actions · Answer 14

Sorry Bru,

I meant this for CintiJohn.

Thanks,

Jatinear:-)