x

How to show row count of a column together with all rows?

I have a table with following structure and data:

id | page | chapter_id

----+--------+------------

1 | page 1 | 1

2 | page 2 | 1

3 | page 3 | 1

4 | page 4 | 2

5 | page 5 | 2

I need to create a sql to produce following result, where "num_of_pages" is the total number of page for a chapter_id:

id | page | chapter_id | num_of_pages

----+--------+--------+-------------------

1 | page 1 | 1 | 3

2 | page 2 | 1 | 3

3 | page 3 | 1 | 3

4 | page 4 | 2 | 2

5 | page 5 | 2 | 2

How do I do that?

Thanks,

more ▼

asked Nov 25, 2014 at 01:05 AM in Default

avatar image

zmeng
1 3

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

Use an aggregate windowed function

 declare @YourTable table (id int, page varchar(10), chapter_id int)
 insert into @YourTable select 1, 'page 1',1
 insert into @YourTable select 2, 'page 2',1
 insert into @YourTable select 3, 'page 3',1
 insert into @YourTable select 4, 'page 4',2
 insert into @YourTable select 5, 'page 5',2
 
 
 select 
     *,
     sum(1)over(partition by chapter_id) as Num_of_Pages
 from @YourTable

gives you

 id          page       chapter_id  Num_of_Pages
 ----------- ---------- ----------- ------------
 1           page 1     1           3
 2           page 2     1           3
 3           page 3     1           3
 4           page 4     2           2
 5           page 5     2           2
 
 (5 row(s) affected)
more ▼

answered Nov 25, 2014 at 08:37 AM

avatar image

Kev Riley ♦♦
66.8k 48 65 81

This is the first time I learned about aggregate windowed function. Perfect answer. Thanks!

Dec 17, 2014 at 05:34 AM zmeng
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x76
x53

asked: Nov 25, 2014 at 01:05 AM

Seen: 216 times

Last Updated: Dec 17, 2014 at 05:34 AM

Copyright 2018 Redgate Software. Privacy Policy