SQL Server Count

Question

I have a situation where I need to count the number of records (Lines) for each job number in the jobtable. I have the following select script which does this.

Select job, COUNT (*) AS 'Lines'
FROM jobtable
GROUP BY job


Job       Lines
--------- --------
1         26

2         12

3         46
...

Now I would like to update a second table "materials" using the result of the count in a column called 'time'. So in the table called materials where the job = 1 update time = 26, where job is = 2 update the time = 12 etc ......

This may help, pretty similar - http://stackoverflow.com/questions/12225715/update-statement-using-join-and-group-by. — thinkster, Jun 04 '14 at 21:24

score 1 · Answer 1 · answered Jun 04 '14 at 21:09

1

You can try like below, joining your materials table with your current query result

UPDATE
    m
SET
    [time] = lines
FROM
    materials m
    JOIN
(
Select job, COUNT (*) AS 'Lines'
FROM jobtable
GROUP BY job
) tab on m.job = tab.job

answered Jun 04 '14 at 21:09

Rahul

76,197
13
71
125

score 0 · Answer 2 · answered Jun 04 '14 at 21:50

In addition to the answer provided by Rahul, if you're on SQL Server 2005 or later, you can use a CTE, which I personally find more readily understandable than constructions using subqueries:

with CountsCTE as
(
    select [job], count(*) as [lines]
    from [jobtable]
    group by [job]
)
update [materials]
set [time] = C.[lines]
from [materials] M
    inner join [CountsCTE] C on M.[job] = C.[job];

If you're on SQL Server 2008 or later, you can replace the UPDATE with a MERGE:

with CountsCTE as
(
    select [job], count(*) as [lines]
    from [jobtable]
    group by [job]
)
merge into [materials] M
    using [CountsCTE] C on M.[job] = C.[job]
    when matched then update set M.[time] = C.[lines];

SQL Server Count

2 Answers2