CODEWITHSUNDEEP
sql-servert-sqlstored-procedures
SBB
SBB

Reputation: 8990

TSQL Updating Table with Counts from Other

I have a stored procedure I am creating and having a little trouble coming up with the logic.

I have a table that contains a list of Queue names such as (Bugs, Support, etc).

I am making a page that is going to show all of the Queues along with the total number of tickets in each one.

I first created a temp table and populated it with the list of queue names. I am now trying to figure out how to update that temp table with a list of all the counts for each of the queue names.

In the example below, there are 4 queues and 1 ticket. However its saying that each queue has 1 ticket which is incorrect.

Any better approach for this?

-- Create a temp table to hold all of the queues and counts
DECLARE @table AS TABLE (
    reqQueue     VARCHAR (100),
    totalRecords INT           NULL);

-- Load our temp table with the data
INSERT INTO @table (reqQueue)
SELECT reqQueue
FROM   apsSupport_queues;

-- Update the counts for each of the queues
UPDATE  @table
    SET totalRecords = (SELECT COUNT(reqID)
                        FROM   apsSupport_tickets AS t
                        WHERE  t.reqQueue = reqQueue)
WHERE   reqQueue = reqQueue;

Upvotes: 1

Views: 38

Answers (1)

dugas
dugas

Reputation: 12493

No need for a temp table:

select reqQueue, count(t.reqId) as TotalRecords
from
apsSupport_queues q
left join
apsSupport_tickets t
on q.reqQueue = t.ReqQueue
group by q.reqQueue

Upvotes: 1

Related Questions