June 16, 2011 at 2:38 pm
IN a table consisting of numbers up to 1000(including missing values) if I write a query that the first 100 to be Cheap and 100 to 500 as medium and 501 to 1000 as high using the CASE statement.
Now I want to find the COUNT of number of things under cheap, number of things under medium and number of things under High . How do I do that.
I am using the COunt but it didnot show up.
Thanks in Advance
June 16, 2011 at 2:48 pm
Something like this?
select
SUM ( case when item <= 100 then 1 else 0 end) as [Below100count],
SUM ( case when item > 100 and item <= 500 then 1 else 0 end) as [100to500count] ,
SUM ( case when item > 100 then 1 else 0 end) as [Above500count]
June 16, 2011 at 3:30 pm
Nope it is not working
Anyways thanks
June 16, 2011 at 3:34 pm
ash0550 (6/16/2011)
Nope it is not workingAnyways thanks
It may not be "working" because only you can see your screen. Please provide sample DDL and DML as well as your expected results in a tabular format so we can build what you see on our side and provide tested code.
If you have any doubts please read this article: http://qa.sqlservercentral.com/articles/Best+Practices/61537/[/url]
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 16, 2011 at 3:34 pm
select BillOfMaterialsID,
case when BillOfMaterialsID <= 100 then 'Cheap'
When BillofMaterialsID between 101 and 500 then 'Medium'
When BillofMaterialsID between 501 and 1000 then 'HIGH'
When BillofMaterialsID between 1001 and 3000 then 'VERYHIGH'
When BillofMaterialsID>3000 then 'EXPENSIVE'
END
from Production.BillOfMaterials
This is the Query I have written. I want the count to be printed in the same table itself.
Say the number of ID below 100 are 67 then in the result 67 should be displayed
June 16, 2011 at 3:38 pm
How about some DDL, DML and some expected results? The article has examples of the items I'm asking for.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 16, 2011 at 3:38 pm
ash0550 (6/16/2011)
select BillOfMaterialsID,case when BillOfMaterialsID <= 100 then 'Cheap'
When BillofMaterialsID between 101 and 500 then 'Medium'
When BillofMaterialsID between 501 and 1000 then 'HIGH'
When BillofMaterialsID between 1001 and 3000 then 'VERYHIGH'
When BillofMaterialsID>3000 then 'EXPENSIVE'
END
from Production.BillOfMaterials
This is the Query I have written. I want the count to be printed in the same table itself.
Say the number of ID below 100 are 67 then in the result 67 should be displayed
You forgot the use SUM(CASE WHEN... THEN 1 ELSE 0 END)
That is where the magic happens.
June 16, 2011 at 3:38 pm
ash0550 (6/16/2011)
Say the number of ID below 100 are 67 then in the result 67 should be displayed
u want all the 67 rows to have the row count value?
June 16, 2011 at 3:48 pm
Ninja's_RGR'us (6/16/2011)
ash0550 (6/16/2011)
select BillOfMaterialsID,case when BillOfMaterialsID <= 100 then 'Cheap'
When BillofMaterialsID between 101 and 500 then 'Medium'
When BillofMaterialsID between 501 and 1000 then 'HIGH'
When BillofMaterialsID between 1001 and 3000 then 'VERYHIGH'
When BillofMaterialsID>3000 then 'EXPENSIVE'
END
from Production.BillOfMaterials
This is the Query I have written. I want the count to be printed in the same table itself.
Say the number of ID below 100 are 67 then in the result 67 should be displayed
You forgot the use SUM(CASE WHEN... THEN 1 ELSE 0 END)
That is where the magic happens.
I tried Using it But didnt workout
June 16, 2011 at 3:52 pm
ash0550 (6/16/2011)
Ninja's_RGR'us (6/16/2011)
ash0550 (6/16/2011)
select BillOfMaterialsID,case when BillOfMaterialsID <= 100 then 'Cheap'
When BillofMaterialsID between 101 and 500 then 'Medium'
When BillofMaterialsID between 501 and 1000 then 'HIGH'
When BillofMaterialsID between 1001 and 3000 then 'VERYHIGH'
When BillofMaterialsID>3000 then 'EXPENSIVE'
END
from Production.BillOfMaterials
This is the Query I have written. I want the count to be printed in the same table itself.
Say the number of ID below 100 are 67 then in the result 67 should be displayed
You forgot the use SUM(CASE WHEN... THEN 1 ELSE 0 END)
That is where the magic happens.
I tried Using it But didnt workout
Third try:
How about some DDL, DML and some expected results?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 16, 2011 at 3:55 pm
ColdCoffee (6/16/2011)
ash0550 (6/16/2011)
Say the number of ID below 100 are 67 then in the result 67 should be displayedu want all the 67 rows to have the row count value?
As of now With the query you helped me with I got the following result
BillOfMaterialsID(No column name)Below100count100to500countAbove500count
1Cheap100
2Cheap100
3Cheap100
4Cheap100
5Cheap100
6Cheap100
7Cheap100
8Cheap100
and it runs upto may be 3800 like that ofcourse they are not in the continous fashion the ID numbers
Something in this way. But I want the exact count at each interval
June 16, 2011 at 3:57 pm
opc.three (6/16/2011)
ash0550 (6/16/2011)
Nope it is not workingAnyways thanks
It may not be "working" because only you can see your screen. Please provide sample DDL and DML as well as your expected results in a tabular format so we can build what you see on our side and provide tested code.
If you have any doubts please read this article: http://qa.sqlservercentral.com/articles/Best+Practices/61537/[/url]
AS on now I'm working on it. I would post with the required things soon if I dont get it
June 16, 2011 at 4:10 pm
You must put a comma between each sum... this is what's missing.
But I can't give a tested solution without the ddl and data.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply