January 16, 2017 at 2:49 am
Hi,
I am using the below query,
select t.range as [Type], count(*) as [Value]
from (
select case
when a_count between 0 and 5 then ' 10'
when a_count between 5 and 10 then '20'
else 30 end as range
from points where type='element') t
group by t.range
Output
----------
Type | Value
-----------|-----------
10 | 65654
20 | 54444
-----------------------
If a_count other than 0 and 5,5 and 10 it should print 0. so I want the output as,
Type | Value
-----------|-----------
10 | 65654
20 | 54444
30 | 0
-----------------------
Please help me to achieve this.
Thanks in Advance,
Regards,
Poornima
January 16, 2017 at 3:01 am
You'll need to use a lookup table.
I've done this with a CTE, but if you have a true lookup table, you should use that. USE DevTestDB;
GO
CREATE TABLE #points (ID INT IDENTITY(1,1),
a_count INT,
[type] VARCHAR(20));
INSERT INTO #points (a_count, [type])
SELECT 2, 'product_quiz'
FROM vTally v
where v.N BETWEEN 1 and 65654;
INSERT INTO #points (a_count, [type])
SELECT 8, 'product_quiz'
FROM vTally v
where v.N BETWEEN 1 and 54444;
GO
WITH Ranges AS (
SELECT 10 AS Range
UNION
SELECT 20 AS Range
UNION
SELECT 30 AS Range)
SELECT R.Range,
COUNT(p.a_count) AS [Value]
FROM Ranges R
LEFT JOIN #points p ON p.[Type]='product_quiz'
AND r.Range = CASE
WHEN p.a_count between 0 and 5 THEN '10'
WHEN p.a_count between 5 and 10 THEN '20'
ELSE 30 END
GROUP BY R.Range;
GO
DROP TABLE #points;
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
January 16, 2017 at 3:51 am
Note that the BETWEEN operator includes both the upper and lower bound and therefore 5 is captured in both the 10 and 20 ranges. Because of the way the CASE expression works (working through the options from top to bottom and stopping as soon as it finds a match), it'll only ever appear in the 10 range.
Building on Thom's solution, I've made the following tweaks:
(1) Put the upper and lower bounds for each row into the lookup table. This means no CASE expression is required. It also means, assuming you create a true lookup table, which I recommend, that you won't have to change your code every time the ranges change, just update the lookup table
(2) Moved the WHERE clause into the join predicate, since its existence turned the outer join into an inner join.
WITH Ranges (LowerBound, UpperBound, Range) AS (
SELECT 0, 5, 10 UNION ALL
SELECT 6, 10, 20 UNION ALL
SELECT 11, 70000, 30 -- use an arbitrarily large number for your upper bound
)
SELECT
r.Range AS [Type]
, COUNT(p.a_count) AS [Value]
FROM Ranges r
LEFT JOIN #points p
ON p.a_count >= r.LowerBound AND p.a_count <= r.UpperBound
AND p.[Type]='product_quiz'
GROUP BY r.Range;
John
January 16, 2017 at 6:59 pm
Hi,
I tested your query but I am getting the output as follows,
Type Value
10 0
20 0
30 0
but My need is, the output should be 0 if the a_count range is not between 0 and 5,5 and 10.
My expected output is
Type Value
10 65654
20 54444
30 0
I will try Jeff's Guide to post data and it will be clearly makes you understand my need.
January 16, 2017 at 8:44 pm
Hi Thom,
I read Jeff's Guide to post code in an effective manner..
Thanks for your help.This really helped me to explain in a better way what my need is.
Please use the sql code below,
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#points','U') IS NOT NULL
DROP TABLE #points
create table #points(ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,[type] nvarchar(max),answer_count int ) --===== All Inserts into the IDENTITY column
SET IDENTITY_INSERT #points ON
--===== Insert the test data into the test table
INSERT INTO #points
(ID,[type],answer_count)
SELECT '8971','product_quiz','9' UNION ALL
SELECT '9431','products','0' UNION ALL
SELECT '9352','product_quiz','10' UNION ALL
SELECT '9453','product_quiz','10' UNION ALL
SELECT '27333','product_quiz','3' UNION ALL
SELECT '9014','product_quiz','9' UNION ALL
SELECT '5226','product_quiz','9' UNION ALL
SELECT '9026','product_quiz','10' UNION ALL
SELECT '9027','product_quiz','10' UNION ALL
SELECT '9456','product_quiz','10' UNION ALL
SELECT '9383','products','0' UNION ALL
SELECT '9384','product_quiz','9' UNION ALL
SELECT '9387','product_quiz','10' UNION ALL
SELECT '9422','product_quiz','9' UNION ALL
SELECT '9461','product_quiz','8' UNION ALL
SELECT '9462','product_quiz','9' UNION ALL
SELECT '9463','product_quiz','9' UNION ALL
SELECT '9464','product_quiz','9' UNION ALL
SELECT '27','product_quiz','1' UNION ALL
SELECT '28','product_quiz','20' UNION ALL ---Appears 20
SELECT '31','product_quiz','4' UNION ALL
SELECT '33','product_quiz','24' UNION ALL ---Appears 24
SELECT '9470','product_quiz','2' UNION ALL
SELECT '346970','product_quiz','2' UNION ALL
SELECT '61','product_quiz','5' UNION ALL
SELECT '131','product_quiz','9' UNION ALL
SELECT '134','product_quiz','9'
--===== Set the identity insert back to normal
SET IDENTITY_INSERT #points ON
select t.range as [score], count(*) as [number_of_occurences]
from (
select case
when answer_count between 0 and 5 then ' 0- 9'
when answer_count between 5 and 10 then '10-19'
else '20-99' end as range
from #points where type='product_quiz') t
group by t.range
--------------------------------------------------------------------------------------------------------------------------------------
The output is,
score number_of_occurences
0- 9 6
10-19 17
20-99 2
i.e.,answer_count has 20 and 24.
suppose if don't have answer_count 20 and 24.
Then the result should show 0 instead of 2.
So I want to have the output as,
score number_of_occurences
0- 9 6
10-19 17
20-99 0
How to achieve this using sql ?
January 17, 2017 at 2:06 am
poornima.s_pdi - Monday, January 16, 2017 6:59 PMHi,
I tested your query but I am getting the output as follows,Type Value
10 0
20 0
30 0but My need is, the output should be 0 if the a_count range is not between 0 and 5,5 and 10.
My expected output is
Type Value
10 65654
20 54444
30 0I will try Jeff's Guide to post data and it will be clearly makes you understand my need.
I hate to question you, but are you SURE that doesn't work, I just used your sample inserts and it works fine for both scenarios.
Jeff's input is quite important though, especially in regards to the overlapping ranges. You're testing for between 0-5, and then 5-10. 5 is in both of those ranges, but will only every be put in the first. Should your ranges be 0-5 6-10 or 0-4 and 5-10.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
January 17, 2017 at 2:13 am
Hi Thom,
I want to delete that one.But i don't find option.
After I read Jeff guide given by you, i understand how to give my entire table in a better way..
So I again explained my need in the above post with my actual table
<b</b
January 17, 2017 at 2:25 am
poornima.s_pdi - Monday, January 16, 2017 8:44 PMselect t.range as [score], count(*) as [number_of_occurences]
from (
select case
when answer_count between 0 and 5 then ' 0- 9'
when answer_count between 5 and 10 then '10-19'
else '20-99' end as range
from #points where type='product_quiz') t
group by t.range
--------------------------------------------------------------------------------------------------------------------------------------
The output is,score number_of_occurences
0- 9 6
10-19 17
20-99 2i.e.,answer_count has 20 and 24.
suppose if don't have answer_count 20 and 24.
Then the result should show 0 instead of 2.
So I want to have the output as,score number_of_occurences
0- 9 6
10-19 17
20-99 0How to achieve this using sql ?
Just put a CASE expression on number_of_occurrences in the first line of your query, such that if range is 20-99 return 0, otherwise return COUNT(*). But let me reiterate: don't do it this way, because every time your ranges change, you'll have to go back to your code. Use a lookup table instead.
John
January 17, 2017 at 2:29 am
poornima.s_pdi - Tuesday, January 17, 2017 2:13 AMHi Thom,I want to delete that one.But i don't find option.
After I read Jeff guide given by you, i understand how to give my entire table in a better way..
So I again explained my need in the above post with my actual table
You can adjust Jeff's, or my, CTE to use the new ranges. You can definitely do this bit yourself.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
January 17, 2017 at 2:37 am
Hi,
Surely I will use CTE or Lookup table as suggested.
I will have a study about these two things and have a try.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply