Help with Counts

  • 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

  • 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

  • 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

  • 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/

  • 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

  • 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

  • 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

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

  • 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

  • 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/

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

  • 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

  • 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'

  • 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

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

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