February 18, 2019 at 1:55 am
create table #testing(
[order] nvarchar(100),
country nvarchar(100),
colour nvarchar(100),
amount int
)
insert into #testing
values('testingOrder1','UK','red',10),
('testingOrder1','UK','red',20),
('testingOrder1','UK','red',30),
('testingOrder2','US','blue',50),
('testingOrder2','US','green',50)
select * from #testing
how can i count distinct partition over in sql
select order, country, colour, amount, expectedCount
,count(distinct colour) over (partition by order,colour)
from #testing
It gives me error on the distinct.
expected result as below:
order country colour amount expectedCount
testingOrder1 UK red 10 1
testingOrder1 UK red 20 1
testingOrder1 UK red 30 1
testingOrder2 US blue 50 2
testingOrder2 US green 50 2
February 18, 2019 at 5:00 am
girl_bj - Monday, February 18, 2019 1:55 AMcreate table #testing(
[order] nvarchar(100),
country nvarchar(100),
colour nvarchar(100),
amount int
)insert into #testing
values('testingOrder1','UK','red',10),
('testingOrder1','UK','red',20),
('testingOrder1','UK','red',30),
('testingOrder2','US','blue',50),
('testingOrder2','US','green',50)select * from #testing
how can i count distinct partition over in sql
select order, country, colour, amount, expectedCount
,count(distinct colour) over (partition by order,colour)
from #testingIt gives me error on the distinct.
expected result as below:
order country colour amount expectedCount
testingOrder1 UK red 10 1
testingOrder1 UK red 20 1
testingOrder1 UK red 30 1
testingOrder2 US blue 50 2
testingOrder2 US green 50 2
Hi Giri,
I think you misunderstood the concept of analytical function. Partition is similar to group by function.
Group by is similar to distinct function.
Example 1 :
select distinct name,city from details
Example 2 :
select name,city from details group by name,city
Both the example 1 and example 2 will give same answer only.
If you used column colour in partition by clause then it is not necessary to use in conjunction with distinct.
Saravanan
February 18, 2019 at 6:14 pm
saravanatn - Monday, February 18, 2019 5:00 AMgirl_bj - Monday, February 18, 2019 1:55 AMcreate table #testing(
[order] nvarchar(100),
country nvarchar(100),
colour nvarchar(100),
amount int
)insert into #testing
values('testingOrder1','UK','red',10),
('testingOrder1','UK','red',20),
('testingOrder1','UK','red',30),
('testingOrder2','US','blue',50),
('testingOrder2','US','green',50)select * from #testing
how can i count distinct partition over in sql
select order, country, colour, amount, expectedCount
,count(distinct colour) over (partition by order,colour)
from #testingIt gives me error on the distinct.
expected result as below:
order country colour amount expectedCount
testingOrder1 UK red 10 1
testingOrder1 UK red 20 1
testingOrder1 UK red 30 1
testingOrder2 US blue 50 2
testingOrder2 US green 50 2Hi Giri,
I think you misunderstood the concept of analytical function. Partition is similar to group by function.
Group by is similar to distinct function.
Example 1 :
select distinct name,city from detailsExample 2 :
select name,city from details group by name,cityBoth the example 1 and example 2 will give same answer only.
If you used column colour in partition by clause then it is not necessary to use in conjunction with distinct.
Hi,
Im thinking to use this method as I have more columns. Would like to make a count.
count(distinct colour) over (partition by order,colour)
February 18, 2019 at 9:09 pm
This?SELECT SortOrder, country, colour, amount
, DENSE_RANK() OVER (ORDER BY SortOrder) AS rn
FROM #testing
ORDER BY SortOrder
, Country
, Colour
, Amount
Oh wait, you're using 2008. Ouch. Might be time for an upgrade?
February 19, 2019 at 5:37 am
girl_bj - Monday, February 18, 2019 1:55 AMcreate table #testing(
[order] nvarchar(100),
country nvarchar(100),
colour nvarchar(100),
amount int
)insert into #testing
values('testingOrder1','UK','red',10),
('testingOrder1','UK','red',20),
('testingOrder1','UK','red',30),
('testingOrder2','US','blue',50),
('testingOrder2','US','green',50)select * from #testing
how can i count distinct partition over in sql
select order, country, colour, amount, expectedCount
,count(distinct colour) over (partition by order,colour)
from #testingIt gives me error on the distinct.
expected result as below:
order country colour amount expectedCount
testingOrder1 UK red 10 1
testingOrder1 UK red 20 1
testingOrder1 UK red 30 1
testingOrder2 US blue 50 2
testingOrder2 US green 50 2
I think this
with dist as
(
select distinct [order], colour
from #testing
),
dist_val as
(select *, count([order]) over (partition by [order]) as expected_amt from dist)
select test.[order],test.country,test.colour,test.amount,expected_amt from dist_val val
inner join #testing test
on val.[order]=test.[order] and val.colour=test.colour
Saravanan
February 19, 2019 at 8:21 am
SELECT
t.*,
--q = COUNT(DISTINCT t.Colour) OVER(PARTITION BY [order]),
x.*
FROM #testing t
CROSS APPLY (SELECT n = COUNT(DISTINCT Colour)
FROM #testing ti WHERE ti.[order] = t.[order]
) x
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
February 19, 2019 at 8:28 am
girl_bj - Monday, February 18, 2019 6:14 PMHi,Im thinking to use this method as I have more columns. Would like to make a count.
count(distinct colour) over (partition by order,colour)
You CANNOT use the DISTINCT keyword in a windowed function.
Also, you have your partition wrong. By definition, all records within a partition have the same value for each of the partition expressions. So a distinct count of one of the partition expressions will always be 1. Specifically in your example, each record in the partition will have the same colour. So the distinct count of colours will always be 1.
In other words, you can't have orange items in a red partition, you can't have yellow items in a red partition, you can't have green items in a red partition, you can't have blue items in a red partition, you can't have indigo items in a red partition, and you can't have purple items in a red partition. You can only have red items in a red partition. The number of distinct colours in a red partition is one: red.
I think this will give you what you're actually looking for. Piet was on the right track with the DENSE_RANK, but he didn't quite go far enough.
select [order], country, colour, amount
, (DENSE_RANK() OVER(PARTITION BY [order] ORDER BY colour) + DENSE_RANK() OVER(PARTITION BY [order] ORDER BY colour DESC) + 1)/2
from #testing
Also, you should really avoid using SQL key words as column names. Order is a SQL key word, and you would be better off using something like order_num instead of just order.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply