SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Help with Counts


Help with Counts

Author
Message
bicky1980
bicky1980
Mr or Mrs. 500
Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)

Group: General Forum Members
Points: 515 Visits: 508
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
lkennedy76
lkennedy76
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3558 Visits: 919
I am a little confused, are you inserting records then wanting to do a count on the table on certain columns?

MCSA SQL Server 2012
bicky1980
bicky1980
Mr or Mrs. 500
Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)

Group: General Forum Members
Points: 515 Visits: 508
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
Cadavre
SSCrazy Eights
SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)

Group: General Forum Members
Points: 9384 Visits: 8492
Look up the OVER clause


Forever 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


Craig Wilkinson - Software Engineer
LinkedIn
lkennedy76
lkennedy76
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3558 Visits: 919
I know your not inserting columns, Smile, 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

MCSA SQL Server 2012
bicky1980
bicky1980
Mr or Mrs. 500
Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)

Group: General Forum Members
Points: 515 Visits: 508
worker bee (1/11/2012)
I know your not inserting columns, Smile, 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
lkennedy76
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3558 Visits: 919
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

MCSA SQL Server 2012
Mark Cowne
Mark Cowne
SSCertifiable
SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)

Group: General Forum Members
Points: 6712 Visits: 25623
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
bicky1980
Mr or Mrs. 500
Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)

Group: General Forum Members
Points: 515 Visits: 508
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
bicky1980
Mr or Mrs. 500
Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)Mr or Mrs. 500 (515 reputation)

Group: General Forum Members
Points: 515 Visits: 508
Mark-101232 (1/11/2012)
worker bee (1/11/2012)
I know your not inserting columns, Smile, 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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search