January 31, 2020 at 11:52 pm
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'.
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:
But below is the result set i am looking for:
How can i achieve this?
February 1, 2020 at 12:13 am
Looks like this might be what you're looking for:
SELECT acc, COUNT(DISTINCT(token)) AS dc
FROM Test1
GROUP BY acc
February 1, 2020 at 7:09 pm
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?
February 1, 2020 at 8:05 pm
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 ...
February 2, 2020 at 5:38 am
SELECT
acc
, COUNT(*) AS numOfAcc
, COUNT(DISTINCT token) AS numOfTokenPerAcc
FROM
dbo.Test1
GROUP BY acc
February 3, 2020 at 12:31 pm
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
Anyone have any advice on the efficiency of this method or if there is a better way?
February 3, 2020 at 2:25 pm
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