Adding a SQL COUNT() result to a column value

Question

I have two tables, Link and Hit. The Hit table contains information about when links from Link were hit. The Link table contains a column HitsCount with the number of times each link has been hit. We regularly aggregate Hit's data into aggregations of data for each hour of hits, and remove the aggregated data to save space. When we aggregate, we want to also update Link.HitsCount with the COUNT() of links for the current hour we are aggregating over. How is this done?

Here is my current attempt, using T-SQL:

DECLARE cc CURSOR LOCAL FOR
    SELECT COUNT(*) AS c, h.LinkId AS id
    FROM   Hit AS h
    WHERE  h.[Timestamp] >= @p_startTime AND h.[Timestamp] < @p_endHour
    GROUP BY h.LinkId

OPEN cc
FETCH cc

UPDATELink
SET Link.HitsCount = HitsCount + c
FROM Link
WHERE CURRENT OF cc

CLOSE cc
DEALLOCATE cc

However, I get the error "Invalid column name 'c'." Also, this approach does not JOIN ON h.LinkId and Link.LinkId like it should. I've thought about dropping and populating a scratch table with the LinkId and COUNT(), but I'd rather avoid this. On the other hand, I'd also rather avoid a CURSOR.

Many thanks.
Nathan

Need to use a variable declare @c int and set to assign

paparazzo
– paparazzo

2013-08-16 21:14:40 +00:00
Commented Aug 16, 2013 at 21:14 — paparazzo
– paparazzo, Commented Aug 16, 2013 at 21:14

T I · Accepted Answer · 2013-08-19 19:03:10Z

3

there is no need for a cursor, you should be doing something like

UPDATE Link
SET HitsCount = HitsCount + (SELECT COUNT(1) FROM Hit 
                             WHERE Hit.LinkId = Link.Id
                             AND Hit.[Timestamp] >= @p_startTime 
                             AND Hit.[Timestamp] < @p_endHour)

or

;WITH Hits AS
(
    SELECT COUNT(1) AS freq, h.LinkId
    FROM   Hit AS h
    WHERE  h.[Timestamp] >= @p_startTime AND h.[Timestamp] < @p_endHour
    GROUP BY h.LinkId
)

UPDATE l
SET l.HitCount = l.HitCount + h.freq
FROM Link l
JOIN Hits h ON h.LinkId = l.Id

edited Aug 19, 2013 at 19:03

answered Aug 16, 2013 at 21:15

T I

9,9734 gold badges32 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Bondolin Over a year ago

The first one worked, with a slight modif of including " < @p_endHour" in the WHERE clause. Thanks!

Barmar · Accepted Answer · 2013-08-16 21:31:20Z

1

Use a JOIN with a subquery:

UPDATE Link l
JOIN (SELECT LinkId, COUNT(*) HitsCount
      FROM Hit
      WHERE h.[Timestamp] >= @p_startTime AND h.[Timestamp] < @p_endHour
      GROUP BY LinkID) h
USING (LinkId)
SET l.HitsCount = l.HitsCount + h.HitsCount

edited Aug 16, 2013 at 21:31

answered Aug 16, 2013 at 21:19

Barmar

789k57 gold badges555 silver badges669 bronze badges

2 Comments

Barmar Over a year ago

Maybe TSQL requires the AS keyword there.

Bondolin Over a year ago

Perhaps. I tried sticking it in there, but couldn't do much. Thanks anyhow.

Collectives™ on Stack Overflow

Adding a SQL COUNT() result to a column value

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related