Help with Counts

Total Total Unqiue Datakey Total Unique Landline Total Unique Mobile Total Unique Emai
-------------------- -------------------- --------------------- ------------------- -----------------
9 6 6 4 3

SELECT COUNT(DISTINCT datakey) OVER(Partition by datakey) AS Uniquedatakey,
COUNT(DISTINCT CASE WHEN landline<>'' THEN landline END) OVER(Partition by datakey) AS Uniquelandline,
COUNT(DISTINCT CASE WHEN mobile<>'' THEN mobile END) OVER(Partition by datakey) AS Uniquemobile,
COUNT(DISTINCT CASE WHEN email<>'' THEN email END)OVER(Partition by datakey) AS Uniqueemail
FROM 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

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

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