Count of distinct rows in SQL

Question

I want to select fixed size of distinct rows (per page) from table + count of all rows (per query). There is my query without count of all rows:

select distinct takenBookTitle, user from Books
where user = 'username'
order by takenBookTitle
offset 0 rows fetch next 3 rows only

But with this result I need to retrieve count of all rows (without offset.. fetch).

If distinct wasn't necessary It would be done with count(*) OVER():

select takenBookTitle, user, count(*) OVER() AS count 
from Books
where user = 'username'
order by takenBookTitle offset 0 rows fetch next 3 rows only

Result:

BookTitle1  userName1   10
BookTitle2  userName2   10
BookTitle3  userName3   10

But I want to count distinct rows but count() OVER()* doesn't allow it.

Any reason why you wouldn't use two queries here, one to get the total result count and another to retrieve individual results? Seems a bit silly to return the same information (the total count of distinct rows) with each result. — smj
– smj, Commented Nov 23, 2016 at 0:17
@smj the total count of distinct rows is the value which is used on client side by my datatable and I need return this value with each select query. Theoreticaly I can use separate query to select this count but in my situation it's not good way. — Ddd
– Ddd, Commented Nov 23, 2016 at 10:44

Kamil Gosciminski · Accepted Answer · 2016-11-23 00:18:35Z

0

You could calculate the count in another query with the same constraints (where clause) and cross join to it, since it only contains one row so the same information will be appended accordingly to every row:

select
  t.*, g.cnt
from (
  select distinct takenBookTitle, user from Books
  where user = 'username'
  order by user offset 0 rows fetch next 3 rows only
  ) t
cross join (
  select count(*) as cnt
  from Books
  where user = 'username'
  ) g

I'm wondering why are you ordering by user if your where clause specifies only one, but you may be using several users for this purpose and are presenting us the simplified query. If not, that order by seems unnecessary

edited Nov 23, 2016 at 0:18

answered Nov 23, 2016 at 0:13

Kamil Gosciminski

17.3k9 gold badges53 silver badges75 bronze badges

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

Collectives™ on Stack Overflow

Count of distinct rows in SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related