T-SQL multiple COUNT help

  • Hello,

    New to the forum and an SQL novice.

    I have a table similar to the below and need to count, firstly the number of each occurrence of 'acc' and in the same result set, count the number of 'token' per 'acc'.

    output3

    Create the test table with the following:

    DROP TABLE IF EXISTS dbo.Test1;

    CREATE TABLE dbo.Test1
    (
    acc VARCHAR(3) NOT NULL,
    token VARCHAR(3) NOT NULL
    );

    INSERT INTO dbo.Test1(acc, token) VALUES('AA1', 'ZZ1');
    INSERT INTO dbo.Test1(acc, token) VALUES('AA1', 'ZZ1');
    INSERT INTO dbo.Test1(acc, token) VALUES('AA1', 'ZZ1');
    INSERT INTO dbo.Test1(acc, token) VALUES('AA1', 'ZZ2');
    INSERT INTO dbo.Test1(acc, token) VALUES('AA2', 'ZZ1');
    INSERT INTO dbo.Test1(acc, token) VALUES('AA3', 'ZZ1');
    INSERT INTO dbo.Test1(acc, token) VALUES('AA3', 'ZZ2');
    INSERT INTO dbo.Test1(acc, token) VALUES('AA3', 'ZZ3');
    INSERT INTO dbo.Test1(acc, token) VALUES('AA3', 'ZZ4');
    INSERT INTO dbo.Test1(acc, token) VALUES('AA4', 'ZZ1');
    INSERT INTO dbo.Test1(acc, token) VALUES('AA4', 'ZZ1');

    I can count the number of each distinct 'acc' using the following:

    SELECT
    acc
    , COUNT(*) AS numOfacc
    FROM
    dbo.Test1
    GROUP BY acc

    Result:

    output2

    But below is the result set i am looking for:

    output

    How can i achieve this?

  • Looks like this might be what you're looking for:

    SELECT acc, COUNT(DISTINCT(token)) AS dc
    FROM Test1
    GROUP BY acc
  • Perfect, thank you.

    I need to run this against a fixed list of tables and UNION ALL the results. I am assuming that a cursor would be most appropriate but have seen a lot of information regarding avoiding cursors where possible. What would be your recommendation?

  • You could insert the results from each query into a temporary table, then query the temporary table:

    CREATE TABLE #Temp
    (
    TableName sysname NOT NULL
    acc nvarchar(10) NOT NULL,
    dc as int NOT NULL
    );

    INSERT INTO Temp(TableName, acc, dc)
    SELECT 'Test1GROUP' TableName,
    acc acc,
    COUNT(DISTINCT(token)) dc
    FROM Test1GROUP BY acc;

    INSERT INTO Temp(TableName, acc, dc)
    SELECT ...

     

  • SELECT
    acc
    , COUNT(*) AS numOfAcc
    , COUNT(DISTINCT token) AS numOfTokenPerAcc
    FROM
    dbo.Test1
    GROUP BY acc
  • After some some time in google and advice from above i used the following to achieve this.

    I used a 'table variable' to hold the list of tables and a while loop with dynamic SQL to insert all the results into a 'temporary table.

    DECLARE @tv_tableList TABLE (id INT
    ,tableName NVARCHAR(20))
    INSERT INTO @tv_tableList VALUES(1,'dbo.Test1'),(2,'dbo.Test2'),(3,'dbo.Test3')


    CREATE TABLE #tempTable_allResults
    (
    tableName NVARCHAR(20),
    acc NVARCHAR(25),
    numOfAcc INT,
    numOfTokenPerAcc INT
    );


    DECLARE @rowCount INT
    SET @rowCount = (SELECT COUNT(tableName) FROM @tv_tableList)


    DECLARE @i INT
    SET @i = 1


    WHILE(@i <= @rowCount)
    BEGIN
    DECLARE @tn SYSNAME
    SELECT
    @tn = tableName
    FROM
    @tv_tableList
    WHERE
    id = @i
    EXEC ('INSERT INTO #tempTable_allResults
    SELECT '' ' + @tn + ' '' AS tableName
    ,acc
    ,COUNT(*)
    ,COUNT(DISTINCT(token))
    FROM ' + @tn + '
    GROUP BY acc')
    SET @i = @i + 1
    END

    SELECT *
    FROM #tempTable_allResults
    ORDER BY tableName DESC, numOfAcc DESC

    DROP TABLE #tempTable_allResults

    sql1

    Anyone have any advice on the efficiency of this method or if there is a better way?

     

  • Looks ok to me. I would only do it with dynamic SQL if the list of tables is going to change. If they are fixed then just hard code an insert for each table.

Viewing 7 posts - 1 through 6 (of 6 total)

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