Help with Counts

Question

Post reply

Help with Counts

bicky1980

SSCrazy

Points: 2079
More actions
January 11, 2012 at 8:19 am

#249495

Hello All
I have a large database which I need to run a lot of counts on.
Using the following code:
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')
I have approx 40 million records with various fields I need to run counts on.
I would need the results to be like the following
Total = 9
Total Unqiue Datakey = 6
Total Unique Landline (1 per unique datakey)= 5
Total Unique Mobile (1 per unique datakey)=5
Total Unique Emai =3
These are just the basic counts, I will need to then run these incoporating the where clause for various variables. If someone could advise of the best way to run these, then at least thats a start. The way I am currently processing these is very long winded and I am sure there are much easier & better ways to run then
Thanks in advance

Viewing 15 posts - 1 through 15 (of 120 total)

You must be logged in to reply to this topic. Login to reply

lkennedy76 SSChampion Points: 13683 More actions · Answer 1

I am a little confused, are you inserting records then wanting to do a count on the table on certain columns?

MCSE SQL Server 2012\2014\2016

bicky1980 SSCrazy Points: 2079 More actions · Answer 2

worker bee (1/11/2012)
I am a little confused, are you inserting records then wanting to do a count on the table on certain columns?

I am not inserting columns (just did that so you could run the code yourself)

I need to run counts on certain columns, but ensuring the values are unique and only 1 per datakey (This is my address ID)

Thanks

Cadavre SSC-Forever Points: 41690 More actions · Answer 3

Look up the OVER clause

Forever trying to learn
My blog - http://www.cadavre.co.uk/
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/

lkennedy76 SSChampion Points: 13683 More actions · Answer 4

I know your not inserting columns, :), After you insert you data into the table you are wanting to count the datakey column, if so then your select stmt would be this;

Select DataKey Count(*) AS DataKey

FROM TableName

MCSE SQL Server 2012\2014\2016

bicky1980 SSCrazy Points: 2079 More actions · Answer 5

worker bee (1/11/2012)
I know your not inserting columns, :), After you insert you data into the table you are wanting to count the datakey column, if so then your select stmt would be this;
Select DataKey Count(*) AS DataKey
FROM TableName

That would just give me the total rows in the table not the figures I am after

Just to expand on what I require:

Total = 9 (total rows)

Total Unqiue Datakey = 6 (total rows ensuring duplicate datakey values are ignored)

Total Unique Landline (1 per unique datakey)= 5 (total rows ensuring duplicate datakey values are ignored and duplicate landline values are ignored and the landline field is populated)

Total Unique Mobile (1 per unique datakey)=5 (total rows ensuring duplicate datakey values are ignored and duplicate moibile values are ignored and the mobile field is populated)

Total Unique Email =3 (total rows ensuring duplicate datakey values are ignored and duplicate email values are ignored and the email field is populated)

Hope this helps make clear what I am after

Thanks

lkennedy76 SSChampion Points: 13683 More actions · Answer 6

Try this to start, I found this on another web site. I googled count figures in sql table.

http://www.w3schools.com/sql/sql_func_round.asp

SELECT DataKey, ROUND(DataKey,0) as DataKey FROM Tablename

MCSE SQL Server 2012\2014\2016

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 7

Is this what you're after?

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

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

bicky1980 SSCrazy Points: 2079 More actions · Answer 8

worker bee (1/11/2012)
Try this to start, I found this on another web site. I googled count figures in sql table.
http://www.w3schools.com/sql/sql_func_round.asp
SELECT DataKey, ROUND(DataKey,0) as DataKey FROM Tablename

I feel you are not understanding my requirements. I know how to run counts, I just want the best way to run the ones I am after.

the round command does not relate to my question the round command is to round a numeric field to the specified number of decimal places...

bicky1980 SSCrazy Points: 2079 More actions · Answer 9

Mark-101232 (1/11/2012)
worker bee (1/11/2012)
I know your not inserting columns, :), After you insert you data into the table you are wanting to count the datakey column, if so then your select stmt would be this;
Select DataKey Count(*) AS DataKey
FROM TableName
Is this what you're after?
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

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

Cadavre SSC-Forever Points: 41690 More actions · Answer 10

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

Forever trying to learn
My blog - http://www.cadavre.co.uk/
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/

bicky1980 SSCrazy Points: 2079 More actions · Answer 11

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...

stephen99999 Ten Centuries Points: 1197 More actions · Answer 12

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

bicky1980 SSCrazy Points: 2079 More actions · Answer 13

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'

lkennedy76 SSChampion Points: 13683 More actions · Answer 14

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. 😀

MCSE SQL Server 2012\2014\2016