sql - How to use group by function? -

sql - How to use group by function? -

i have table each id pk has several tasks_code. trying grouping relative tasks_code id. when executed below code, group by function didn't work. how utilize grouping function if need grouping 2 columns, select multiple columns display?

select i.id, p.tasks_code, count(*) problem p left bring together id_code on p.id = i.id grouping i.id

the result returns as:

id tasks_code count(*) 1 1 1 2 3 1 1 2 1 1 4 1 2 5 1 1 9 1

i want homecoming result as:

id count(*) 1 4 2 2

place field name within count , grouping id.

select i.id, count(p.tasks_code) count_codes problem p left bring together id_code on p.tasks_code = i.id grouping i.id wanted give quick test , way add together tool belt. count(fld) works can utilize count() over() window function. same result. -- simple test select * #test ( values (1, 1, 1), (2, 3, 1), (1, 2, 1), (1, 4, 1), (2, 5, 1), (1, 9, 1) ) work(id, task, cnt); go -- grouping solution select id, count(task) task_cnt #test grouping id; -- windowing solution select distinct id, count(*) over(partition id) task_cnt #test -- remove temp table drop table #test;

sql sql-server tsql