Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

Help with Counts Expand / Collapse
Author
Message
Posted Wednesday, January 11, 2012 9:32 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 5:08 AM
Points: 2,379, Visits: 7,583
I can't quite match your requested results, can you double check them?

I make it: -
Total                Total Unqiue Datakey Total Unique Landline Total Unique Mobile Total Unique Emai
-------------------- -------------------- --------------------- ------------------- -----------------
9 6 6 4 3



Not a DBA, just trying to learn

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1234121
Posted Wednesday, January 11, 2012 10:10 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 2:01 AM
Points: 215, Visits: 429
Cadavre (1/11/2012)
I can't quite match your requested results, can you double check them?

I make it: -
Total                Total Unqiue Datakey Total Unique Landline Total Unique Mobile Total Unique Emai
-------------------- -------------------- --------------------- ------------------- -----------------
9 6 6 4 3


Oh Dear, stats should be:

Total = 9
Total Unqiue Datakey = 6
Total Unique Landline = 6
Total Unique Mobile =5
Total Unique Emai =3

So the figure for Mobiles is still incorrect...
Post #1234167
Posted Wednesday, January 11, 2012 10:20 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Sunday, November 18, 2012 10:58 PM
Points: 63, Visits: 216
Have not tested this, but :

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

Post #1234176
Posted Wednesday, January 11, 2012 10:30 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 2:01 AM
Points: 215, Visits: 429
stephen99999 (1/11/2012)
Have not tested this, but :

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



Incorrect syntax near 'distinct'
Post #1234183
Posted Wednesday, January 11, 2012 10:53 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, September 18, 2014 10:25 AM
Points: 301, Visits: 595
SELECT COUNT(*) AS Total,
COUNT(DISTINCT datakey) AS Uniquedatakey,
COUNT(DISTINCT CASE WHEN landline<>'' THEN landline END) AS Uniquelandline, COUNT(DISTINCT CASE WHEN mobile<>'' THEN mobile END) AS Uniquemobile, COUNT(DISTINCT CASE WHEN email<>'' THEN email END) AS Uniqueemail
FROM test
GROUP BY (COLUMNS NAMES)
HAVING COUNT (Uniquedatakey)

I may totally understand what you are after but I am trying to help ya out.


MCSA SQL Server 2012
Post #1234209
Posted Wednesday, January 11, 2012 11:05 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Sunday, November 18, 2012 10:58 PM
Points: 63, Visits: 216
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

Post #1234218
Posted Wednesday, January 11, 2012 1:24 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 2:01 AM
Points: 215, Visits: 429
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.
Post #1234334
Posted Wednesday, January 11, 2012 2:05 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Sunday, November 18, 2012 10:58 PM
Points: 63, Visits: 216
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
Post #1234367
Posted Wednesday, January 11, 2012 2:19 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 2:01 AM
Points: 215, Visits: 429
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...
Post #1234381
Posted Wednesday, January 11, 2012 2:35 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Sunday, November 18, 2012 10:58 PM
Points: 63, Visits: 216
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:
datakey Uniquedatakey Uniquelandline Uniquemobile Uniqueemail
0001 2 2 2 2
0002 3 3 3 3
0003 1 1 1 1
0004 1 1 1 1
0005 1 1 1 1
0008 1 1 1 1

Are we getting close?
Post #1234393
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse