January 11, 2012 at 11:05 am
Try this bick, and please reply with the results... I am not on a machine with management studio, so can't test.
SELECT datakey,COUNT(datakey) OVER(Partition by datakey) AS Uniquedatakey,
COUNT(landline) OVER(Partition by datakey) AS Uniquelandline,
COUNT(mobile) OVER(Partition by datakey) AS Uniquemobile,
COUNT(emailD)OVER(Partition by datakey) AS Uniqueemail
FROM test
group by datakey
January 11, 2012 at 1:24 pm
stephen99999 (1/11/2012)
Try this bick, and please reply with the results... I am not on a machine with management studio, so can't test.
SELECT datakey,COUNT(datakey) OVER(Partition by datakey) AS Uniquedatakey,
COUNT(landline) OVER(Partition by datakey) AS Uniquelandline,
COUNT(mobile) OVER(Partition by datakey) AS Uniquemobile,
COUNT(emailD)OVER(Partition by datakey) AS Uniqueemail
FROM test
group by datakey
Column 'test.landline' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
January 11, 2012 at 2:05 pm
bicky1980 (1/11/2012)
stephen99999 (1/11/2012)
Try this bick, and please reply with the results... I am not on a machine with management studio, so can't test.
SELECT datakey,COUNT(datakey) OVER(Partition by datakey) AS Uniquedatakey,
COUNT(landline) OVER(Partition by datakey) AS Uniquelandline,
COUNT(mobile) OVER(Partition by datakey) AS Uniquemobile,
COUNT(emailD)OVER(Partition by datakey) AS Uniqueemail
FROM test
group by datakey
Column 'test.landline' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Either remove the GROUP BY or add the necessary columns to the GROUP BY (the errors will tell you). Or instead of the GROUP BY, just add DISTINCT after SELECT.
Please reply with results/errors.
Also, are you not debugging? Really try to work through these issues before replying. This will show the more expert posters that you are trying to learn instead of just get a direct answer.
-Stephen
January 11, 2012 at 2:19 pm
stephen99999 (1/11/2012)
bicky1980 (1/11/2012)
stephen99999 (1/11/2012)
Try this bick, and please reply with the results... I am not on a machine with management studio, so can't test.
SELECT datakey,COUNT(datakey) OVER(Partition by datakey) AS Uniquedatakey,
COUNT(landline) OVER(Partition by datakey) AS Uniquelandline,
COUNT(mobile) OVER(Partition by datakey) AS Uniquemobile,
COUNT(emailD)OVER(Partition by datakey) AS Uniqueemail
FROM test
group by datakey
Column 'test.landline' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Either remove the GROUP BY or add the necessary columns to the GROUP BY (the errors will tell you). Or instead of the GROUP BY, just add DISTINCT after SELECT.
Please reply with results/errors.
Also, are you not debugging? Really try to work through these issues before replying. This will show the more expert posters that you are trying to learn instead of just get a direct answer.
-Stephen
Thanks for the reply... you did ask me to tell you the results, so that is what I did. I am still trying to solve the problem myself too, not just waiting for an solution (if there is one)
I have never used the debugger (will look into that) - Thanks for the advice. I think I need to use a mixture of the over(), partition(), row_number() and maybe rank() clauses...Maybe...
January 11, 2012 at 2:35 pm
Alright, lets try this
declare @t table(indkey nvarchar(2),datakey nvarchar(4),landline nvarchar(11),mobile nvarchar(11),email nvarchar(20))
insert into @t values ('1','0001','01234567890','0712345679','1@test.co.uk')
insert into @t values('2','0001','01234567890','','1@test.co.uk')
insert into @t values('3','0002','01234567891','','2@test.co.uk')
insert into @t values('4','0002','01234567890','','2@test.co.uk')
insert into @t values('5','0002','','07123456789','')
insert into @t values('6','0003','01234567892','07123456791','')
insert into @t values('7','0004','01234567893','07123456792','')
insert into @t values('8','0005','01234567894','07123456793','2@test.co.uk')
insert into @t values('9','0008','01234567895','07123456793','9@test.co.uk')
SELECT distinct datakey,COUNT(datakey) OVER(Partition by datakey) AS Uniquedatakey,
COUNT(landline) OVER(Partition by datakey) AS Uniquelandline,
COUNT(mobile) OVER(Partition by datakey) AS Uniquemobile,
COUNT(email)OVER(Partition by datakey) AS Uniqueemail
FROM @t
Results:
datakeyUniquedatakeyUniquelandlineUniquemobileUniqueemail
00012222
00023333
00031111
00041111
00051111
00081111
Are we getting close?
January 11, 2012 at 2:59 pm
The query certainly groups the results by the datakey
but all the values are the same...
January 11, 2012 at 3:10 pm
Modified stephen's code a bit:
declare @t table(indkey nvarchar(2),datakey nvarchar(4),landline nvarchar(11),mobile nvarchar(11),email nvarchar(20))
insert into @t values ('1','0001','01234567890','0712345679','1@test.co.uk')
insert into @t values('2','0001','01234567890','','1@test.co.uk')
insert into @t values('3','0002','01234567891','','2@test.co.uk')
insert into @t values('4','0002','01234567890','','2@test.co.uk')
insert into @t values('5','0002','','07123456789','')
insert into @t values('6','0003','01234567892','07123456791','')
insert into @t values('7','0004','01234567893','07123456792','')
insert into @t values('8','0005','01234567894','07123456793','2@test.co.uk')
insert into @t values('9','0008','01234567895','07123456793','9@test.co.uk')
SELECT distinct datakey,COUNT(datakey) OVER(Partition by datakey) AS Uniquedatakey,
COUNT(*) OVER(Partition by landline, datakey) AS Uniquelandline,
COUNT(*) OVER(Partition by mobile, datakey) AS Uniquemobile,
COUNT(*)OVER(Partition by email, datakey) AS Uniqueemail
FROM @t
Would that work?
Best regards,
Best regards,
Andre Guerreiro Neto
Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA
January 11, 2012 at 3:37 pm
No, its not performing the counts I need to get back (thanks for the effort though)
Total = 9
Total Unqiue Datakey = 6
Total Unique Landline = 6
Total Unique Mobile =5
Total Unique Emai =3
January 11, 2012 at 4:01 pm
I understand that something like this should work:
COUNT(*) OVER(Partition by datakey) / COUNT(*) OVER(Partition by landline, datakey) AS Uniquelandline,
COUNT(*) OVER(Partition by datakey) / COUNT(*) OVER(Partition by mobile, datakey) AS Uniquemobile,
COUNT(*) OVER(Partition by datakey) / COUNT(*)OVER(Partition by email, datakey) AS Uniqueemail
But I can't seem to get DISTINCT to behave as I want. Maybe someone more experience could shed a light on this?
Best regards,
Best regards,
Andre Guerreiro Neto
Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA
January 11, 2012 at 4:16 pm
Hello All
I think I have managed to run the counts individually
select count(*) from test
select count(distinct datakey) as Unique_Dataset
from (select *, row_number() over(partition by datakey order by case when Datakey!='' then 0 else 1 end) as pref
from test where Datakey!=''
) z where pref = 1
select count(distinct landline) as Unique_landlines
from (select *, row_number() over(partition by datakey order by case when landline!='' then 0 else 1 end) as pref
from test where landline!=''
) z where pref = 1
select count(distinct mobile)
from (select *, row_number() over(partition by datakey order by case when mobile!='' then 0 else 1 end) as pref
from test where mobile!=''
) z where pref = 1
select count(distinct email) as Unique_Emails
from (select *, row_number() over(partition by datakey order by case when email!='' then 0 else 1 end) as pref
from test where email!=''
) z where pref = 1
I think now I just need to group these statements together into one statement...Any Suggestions
January 11, 2012 at 5:25 pm
bicky1980 (1/11/2012)
Hello AllI think now I just need to group these statements together into one statement...Any Suggestions
UNION ALL with a static data column in each statement to say which count it belonged to?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 11, 2012 at 6:52 pm
@bicky
It has to be one of these two options lol. For each option, I started out with a quote on your desired results should be, as well as some previous requirements you stated.
Option 1:
No, its not performing the counts I need to get back (thanks for the effort though)
Total = 9
Total Unqiue Datakey = 6
Total Unique Landline = 6
Total Unique Mobile =5
Total Unique Emai =3
declare @t table(indkey nvarchar(2),datakey nvarchar(4),landline nvarchar(11),mobile nvarchar(11),email nvarchar(20))
insert into @t values ('1','0001','01234567890','0712345679','1@test.co.uk')
insert into @t values('2','0001','01234567890','','1@test.co.uk')
insert into @t values('3','0002','01234567891','','2@test.co.uk')
insert into @t values('4','0002','01234567890','','2@test.co.uk')
insert into @t values('5','0002','','07123456789','')
insert into @t values('6','0003','01234567892','07123456791','')
insert into @t values('7','0004','01234567893','07123456792','')
insert into @t values('8','0005','01234567894','07123456793','2@test.co.uk')
insert into @t values('9','0008','01234567895','07123456793','9@test.co.uk');
selectcount(datakey) Total,
count(distinct datakey) Uniquedatakey ,
COUNT(distinct case when landline <>'' then landline end) Uniquelandline,
COUNT(distinct case when mobile <>'' then mobile end) Uniquemobile,
COUNT(distinct case when email <>'' then email end) Uniqueemail
from @t
TotalUniquedatakeyUniquelandlineUniquemobileUniqueemail
96653
Option 2:
Nearly...
the counts for landline, mobile & email also need to be group by datakey - so the figure for landline must be a unique landline as well as a unique datakey
Thanks
selectcount(datakey) Total,
count(distinct datakey) Uniquedatakey ,
COUNT(distinct datakey + case when landline <> '' then landline end) Uniquelandline,
COUNT(distinct datakey + case when mobile <>'' then mobile end) Uniquemobile,
COUNT(distinct datakey + case when email <>'' then email end) Uniqueemail
from @t
TotalUniquedatakeyUniquelandlineUniquemobileUniqueemail
96764
January 12, 2012 at 2:15 am
stephen99999 (1/11/2012)
@bickyIt has to be one of these two options lol. For each option, I started out with a quote on your desired results should be, as well as some previous requirements you stated.
Option 1:
No, its not performing the counts I need to get back (thanks for the effort though)
Total = 9
Total Unqiue Datakey = 6
Total Unique Landline = 6
Total Unique Mobile =5
Total Unique Emai =3
declare @t table(indkey nvarchar(2),datakey nvarchar(4),landline nvarchar(11),mobile nvarchar(11),email nvarchar(20))
insert into @t values ('1','0001','01234567890','0712345679','1@test.co.uk')
insert into @t values('2','0001','01234567890','','1@test.co.uk')
insert into @t values('3','0002','01234567891','','2@test.co.uk')
insert into @t values('4','0002','01234567890','','2@test.co.uk')
insert into @t values('5','0002','','07123456789','')
insert into @t values('6','0003','01234567892','07123456791','')
insert into @t values('7','0004','01234567893','07123456792','')
insert into @t values('8','0005','01234567894','07123456793','2@test.co.uk')
insert into @t values('9','0008','01234567895','07123456793','9@test.co.uk');
selectcount(datakey) Total,
count(distinct datakey) Uniquedatakey ,
COUNT(distinct case when landline <>'' then landline end) Uniquelandline,
COUNT(distinct case when mobile <>'' then mobile end) Uniquemobile,
COUNT(distinct case when email <>'' then email end) Uniqueemail
from @t
TotalUniquedatakeyUniquelandlineUniquemobileUniqueemail
96653
Option 2:
Nearly...
the counts for landline, mobile & email also need to be group by datakey - so the figure for landline must be a unique landline as well as a unique datakey
Thanks
selectcount(datakey) Total,count(distinct datakey) Uniquedatakey ,
COUNT(distinct datakey + case when landline <> '' then landline end) Uniquelandline,
COUNT(distinct datakey + case when mobile <>'' then mobile end) Uniquemobile,
COUNT(distinct datakey + case when email <>'' then email end) Uniqueemail
from @t
TotalUniquedatakeyUniquelandlineUniquemobileUniqueemail
96764
The logic for option 1 doesnt appear to be correct (the counts are not grouping the results by unique dataset) and option 2 returns incorrect results...
January 12, 2012 at 2:31 am
BEGIN TRAN
CREATE TABLE test (indkey NVARCHAR(2), datakey NVARCHAR(4), landline NVARCHAR(11), mobile NVARCHAR(11), email NVARCHAR(20))
INSERT INTO test
VALUES ('1', '0001', '01234567890', '0712345679', '1@test.co.uk')
INSERT INTO test
VALUES ('2', '0001', '01234567890', '', '1@test.co.uk')
INSERT INTO test
VALUES ('3', '0002', '01234567891', '', '2@test.co.uk')
INSERT INTO test
VALUES ('4', '0002', '01234567890', '', '2@test.co.uk')
INSERT INTO test
VALUES ('5', '0002', '', '07123456789', '')
INSERT INTO test
VALUES ('6', '0003', '01234567892', '07123456791', '')
INSERT INTO test
VALUES ('7', '0004', '01234567893', '07123456792', '')
INSERT INTO test
VALUES ('8', '0005', '01234567894', '07123456793', '2@test.co.uk')
INSERT INTO test
VALUES ('9', '0008', '01234567895', '07123456793', '9@test.co.uk')
SELECT MAX(total) AS Total, COUNT(DISTINCT datakey) AS [Total Unqiue Datakey],
COUNT(DISTINCT datakey + CASE WHEN landline <> '' THEN landline END) AS [Total Unique Landline],
COUNT(DISTINCT datakey + CASE WHEN mobile <> '' THEN mobile END) AS [Total Unique Mobile],
COUNT(DISTINCT datakey + CASE WHEN email <> '' THEN email END) AS [Total Unique Email]
FROM (SELECT indkey, datakey, landline, mobile, email,
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS total,
ROW_NUMBER() OVER (PARTITION BY datakey ORDER BY landline DESC, mobile DESC, email DESC) AS partitionSet
FROM test) innerQ
WHERE partitionSet = 1
ROLLBACK
Total Total Unqiue Datakey Total Unique Landline Total Unique Mobile Total Unique Email
-------------------- -------------------- --------------------- ------------------- ------------------
9 6 6 5 4
January 12, 2012 at 6:25 am
in table format, bicky, please post what you are expecting your results to look like. Earlier you stated you wanted to see 9, 6, etc to be returned, and now you say it is not.
Just confused of your requirements.
Viewing 15 posts - 16 through 30 (of 120 total)
You must be logged in to reply to this topic. Login to reply