Geek Answers Handbook

Select an ID, counter (ID) and group by date

I have an article table in which there are columns id and date (month / year), first of all, I would like to calculate identifiers and group them by date, then I would like to see which identifier belongs to that group of dates in one query like this:

id   date   count
-----------------
1    01/2015   2
2    01/2015   2
3    02/2015   1
4    03/2015   4
5    03/2015   4
6    03/2015   4
7    03/2015   4

I have 2 questions

Select Count(id) 
from article 
group by date

and

Select id 
from article

gives results;

count date            id  date
-------------         ----------
2     01/2015         1  01/2015
1     02/2015         2  01/2015 
4     03/2015         3  02/2015

I need one request like

select count(id), id, date 
from....

which adds id, count, date columns for use in my C # code.

Can someone help me?

+4
source share
4 answers
SELECT id,
       date,
       COUNT(*) OVER (PARTITION BY date) AS Count
FROM article

Sql violin

+2
source

, CTE :

create table #tt (id int null, dt varchar(8))
insert #tt values
(1,'01/2015'),
(2,'01/2015'),
(3,'02/2015'),
(4,'03/2015'),
(5,'03/2015'),
(6,'03/2015'),
(7,'03/2015')

;with cteCount(d, c) AS
(
    select dt, count(id) from #tt group by dt
)
select id, dt, c
from #tt a
inner join cteCount cc
on a.dt = cc.d

drop table #tt

:

id  dt      c
1   01/2015 2
2   01/2015 2
3   02/2015 1
4   03/2015 4
5   03/2015 4
6   03/2015 4
7   03/2015 4
+1
if not exists(select * from TEST.sys.objects where type=N'U' and name=N'article')
begin
    create table article(
    [id] int,
    [date] date)
end

:

insert into article(id,date) values(1,convert(date,'15/01/2015',103));
insert into article(id,date) values(1,convert(date,'15/02/2015',103));
insert into article(id,date) values(2,convert(date,'15/03/2015',103));
insert into article(id,date) values(2,convert(date,'15/01/2015',103));
insert into article(id,date) values(3,convert(date,'15/02/2015',103));
insert into article(id,date) values(4,convert(date,'15/03/2015',103));
insert into article(id,date) values(5,convert(date,'15/01/2015',103));
insert into article(id,date) values(5,convert(date,'15/02/2015',103));
insert into article(id,date) values(1,convert(date,'15/03/2015',103));
insert into article(id,date) values(2,convert(date,'15/01/2015',103));
insert into article(id,date) values(3,convert(date,'15/02/2015',103));
insert into article(id,date) values(4,convert(date,'15/03/2015',103));
insert into article(id,date) values(5,convert(date,'15/01/2015',103));
insert into article(id,date) values(1,convert(date,'15/02/2015',103));
insert into article(id,date) values(2,convert(date,'15/03/2015',103));
insert into article(id,date) values(3,convert(date,'15/01/2015',103));
insert into article(id,date) values(4,convert(date,'15/03/2015',103));

select id,[date], count(id) [count] from article
group by [date],[id]

the result:

id  date    count
1   2015-01-15  1
1   2015-02-15  2
1   2015-03-15  1
2   2015-01-15  2
2   2015-03-15  2
3   2015-01-15  1
3   2015-02-15  2
4   2015-03-15  3
5   2015-01-15  2
5   2015-02-15  1
0

It is not clear how you want to generate the id field as a result. if you want to generate it manually, then use RANK()or if you want to get it from the table idvalue, you can use max()or min()(depends on the expected result)

Use RANK() Script Demo here

Try:

create table tt (id int null, dt varchar(8),count int)
insert tt values
(1,'01/2015',2),
(2,'01/2015',2),
(3,'02/2015',1),
(4,'03/2015',4),
(5,'03/2015',4),
(6,'03/2015',4),
(7,'03/2015',4)

Query:

 select count(id) as count,dt,RANK() 
    over(order by count(id)) as id from tt group by dt

EDIT2: either you can use max()ormin()

as:

select count(id) as count,dt,Min(id) as id from tt group by dt

or

select count(id) as count,dt,MAX(id) as id from tt group by dt
0
source

More articles:


All Articles