T-SQL multiple COUNT help

Question

Post reply

T-SQL multiple COUNT help

scienceof8

Old Hand

Points: 349

January 31, 2020 at 11:52 pm

#3722179

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?

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

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

pietlinden SSC Guru Points: 63471 More actions · Answer 1

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

SELECT acc, COUNT(DISTINCT(token)) AS dc
FROM Test1
GROUP BY acc

scienceof8 Old Hand Points: 349 More actions · Answer 2

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?

Jonathan AC Roberts SSCoach Points: 18328 More actions · Answer 3

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

DesNorton SSC-Insane Points: 24352 More actions · Answer 4

SELECT
acc
, COUNT(*) AS numOfAcc
, COUNT(DISTINCT token) AS numOfTokenPerAcc
FROM 
dbo.Test1
GROUP BY acc

How to post data/code on a forum to get the best help.

scienceof8 Old Hand Points: 349 More actions · Answer 5

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?

Jonathan AC Roberts SSCoach Points: 18328 More actions · Answer 6

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.