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 8:19 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, March 31, 2014 9:45 AM
Points: 213, Visits: 399
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
Post #1234021
Posted Wednesday, January 11, 2012 8:31 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, March 14, 2014 10:32 AM
Points: 215, Visits: 444
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
Post #1234038
Posted Wednesday, January 11, 2012 8:36 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, March 31, 2014 9:45 AM
Points: 213, Visits: 399
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
Post #1234043
Posted Wednesday, January 11, 2012 8:37 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 4:55 AM
Points: 2,403, Visits: 7,310
Look up the OVER clause


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 #1234044
Posted Wednesday, January 11, 2012 8:49 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, March 14, 2014 10:32 AM
Points: 215, Visits: 444
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


MCSA SQL Server 2012
Post #1234058
Posted Wednesday, January 11, 2012 8:58 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, March 31, 2014 9:45 AM
Points: 213, Visits: 399
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


Post #1234069
Posted Wednesday, January 11, 2012 9:05 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, March 14, 2014 10:32 AM
Points: 215, Visits: 444
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
Post #1234077
Posted Wednesday, January 11, 2012 9:09 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 6:13 AM
Points: 1,694, Visits: 19,550

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



____________________________________________________

How to get the best help on a forum

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

Never approach a goat from the front, a horse from the rear, or a fool from any direction.
Post #1234085
Posted Wednesday, January 11, 2012 9:11 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, March 31, 2014 9:45 AM
Points: 213, Visits: 399
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...
Post #1234087
Posted Wednesday, January 11, 2012 9:15 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, March 31, 2014 9:45 AM
Points: 213, Visits: 399
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
Post #1234096
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse