# Have Data Explorer Show number of results?
**URL:** https://meta.discourse.org/t/have-data-explorer-show-number-of-results/96687
**Category:** Feature
**Created:** [September 7, 2018, 10:34pm UTC](https://meta.discourse.org/t/have-data-explorer-show-number-of-results/96687 "2018-09-07T22:34:50Z")
**Posts on this page:** 14
**Page:** 1
### Author:  [@pfaffman](https://meta.discourse.org/u/pfaffman)
#### Post date: [September 7, 2018, 10:34pm UTC](https://meta.discourse.org/t/have-data-explorer-show-number-of-results/96687/1 "2018-09-07T22:34:50Z")
[Data explorer](https://meta.discourse.org/t/32566?silent=true) returns only a certain number (500?) of items in the web interface. Too see them all you need to download them and look at them some other way (i.e., download a csv and open it in a spreadsheet).
It would be nice if it were easy to know how many items had been returned. What I’ve done in the past is to generate a second query that just did the count, which I find rather painful.
### Feature Request
When the query is completed, would it be easy to pull out the number of results and print it next to (or below) the query completion time?
---
### Author:  [@sam](https://meta.discourse.org/u/sam)
#### Post date: [September 10, 2018, 6:52am UTC](https://meta.discourse.org/t/have-data-explorer-show-number-of-results/96687/2 "2018-09-10T06:52:52Z")
Sure, we should do something here @rishabh
When you run a query it should show `Query completed in 12.7 ms, 102 results`
IF we are truncating due to our enforced limit eg try `SELECT id, raw from posts` on meta. we should say `Query completed in 12.7 ms, partial results, top 500 returned` … something like that.
---
### Author:  [@pfaffman](https://meta.discourse.org/u/pfaffman)
#### Post date: [September 10, 2018, 11:18am UTC](https://meta.discourse.org/t/have-data-explorer-show-number-of-results/96687/4 "2018-09-10T11:18:06Z")
That’s an improvement, but the specific problem I’m trying to solve is that if there are more than 500,the only way to find out how many there are is to write another query or download the data set to a CSV and count the rows (and subtract one).
I’m not sure what the solution there would be.
---
### Author:  [@Mittineague](https://meta.discourse.org/u/Mittineague)
#### Post date: [September 10, 2018, 6:43pm UTC](https://meta.discourse.org/t/have-data-explorer-show-number-of-results/96687/5 "2018-09-10T18:43:59Z")
I think what you are wanting is a `SELECT COUNT()` query.
I think for many queries it could be a matter of simply replacing the main SELECT fields with COUNT. But I don’t know if it would get messy with queries that had aggregates and GROUP BYs.
IMHO, a type of “results were limited to 500” would be good enough and would indicate that a separate COUNT query might be needed.
I quite often put together COUNT queries when I work up a query so I won’t be taken by surprise by a long running query. eg. from this table with mega rows join another table with mega rows join yet another table with mega rows …
In fact, I would recommend running heavy queries on a _copy_ of the live database to avoid having the forum hang while the query runs.
---
### Author:  [@pfaffman](https://meta.discourse.org/u/pfaffman)
#### Post date: [September 10, 2018, 6:47pm UTC](https://meta.discourse.org/t/have-data-explorer-show-number-of-results/96687/6 "2018-09-10T18:47:10Z")
> [@Mittineague](#):
>
> I think what you are wanting is a `SELECT COUNT()` query.
That’s what I want, but the only way that I know to do that is to create a whole separate query that does the `SELECT COUNT`. So I’d need to create a query for (e.g., “closed topics” and “closed topics count”).
---
### Author:  [@codinghorror](https://meta.discourse.org/u/codinghorror)
#### Post date: [September 10, 2018, 8:43pm UTC](https://meta.discourse.org/t/have-data-explorer-show-number-of-results/96687/7 "2018-09-10T20:43:35Z")
Just so you know, this is quite standard in SQL databases. You generally don’t get a count unless you _explicitly_ ask for it, because **counting “all the results” can be more expensive than getting the first {x} records**.
We’re not secretly withholding information that we regularly get back from the database, because we’re jerks 😉
---
### Author:  [@riking](https://meta.discourse.org/u/riking)
#### Post date: [September 13, 2018, 3:37am UTC](https://meta.discourse.org/t/have-data-explorer-show-number-of-results/96687/8 "2018-09-13T03:37:20Z")
I think a button for “Wrap the query with count() and get the result” might be viable.
```plaintext
WITH query AS (
#{sql}
) SELECT COUNT(*) FROM query
-- do not include LIMIT clause inside `sql`
```
---
### Author:  [@pfaffman](https://meta.discourse.org/u/pfaffman)
#### Post date: [September 13, 2018, 12:00pm UTC](https://meta.discourse.org/t/have-data-explorer-show-number-of-results/96687/9 "2018-09-13T12:00:48Z")
That’s brilliant! That might be a PR that I could pull off (though there is a fairly long list of things that I need to submit).
---
### Author:  [@Mittineague](https://meta.discourse.org/u/Mittineague)
#### Post date: [September 13, 2018, 5:25pm UTC](https://meta.discourse.org/t/have-data-explorer-show-number-of-results/96687/10 "2018-09-13T17:25:27Z")
Would this in effect be running the query twice? Once to get the row count without the built-in resource use safety measures, and then again with the limit.
EDIT
AFAIK, though the [Data Explorer](https://meta.discourse.org/t/32566?silent=true) plugin has a built in LIMIT, it does not have an OFFSET.
I think if there was some way to return the 500 result rows in “batches” - maybe with a OFFSET or a “not included in prior results” condition? - it would be safer. TBH, I’m thinking the better way would be to simply show a type of “LIMIT met, if you want more do it from the CLI” message where the admin could do a SELECT into an outfile query.
---
### Author:  [@Mittineague](https://meta.discourse.org/u/Mittineague)
#### Post date: [September 13, 2018, 6:43pm UTC](https://meta.discourse.org/t/have-data-explorer-show-number-of-results/96687/11 "2018-09-13T18:43:18Z")
This looks promising
[https://www.sitepoint.com/community/t/getting-the-number-of-rows-that-a-postgresql-select-would-return-without-a-limit-from-a-query-with-a-limit/306558/2?u=mittineague](https://www.sitepoint.com/community/t/getting-the-number-of-rows-that-a-postgresql-select-would-return-without-a-limit-from-a-query-with-a-limit/306558/2?u=mittineague)
[PostgreSQL: Documentation: 9.1: Value Expressions](https://www.postgresql.org/docs/9.1/static/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS)
> the window function is able to scan all the rows that would be part of the current row’s group according to the grouping specification
EDIT
I just had a brief play on my localhost as a POC
```plaintext
SELECT users.username
, COUNT(users.username) OVER ()
FROM users
WHERE users.id > 4
LIMIT 5
```
---
### Author:  [@rishabh](https://meta.discourse.org/u/rishabh)
#### Post date: [September 18, 2018, 6:20pm UTC](https://meta.discourse.org/t/have-data-explorer-show-number-of-results/96687/12 "2018-09-18T18:20:15Z")
For a first phase, the result limit has been [updated from 250 to 1000](https://github.com/discourse/discourse-data-explorer/commit/f876cf66f1984a55c91ee5cdb53da2ce414faea7) and number of results is displayed via:
[https://github.com/discourse/discourse-data-explorer/commit/316923f1906d811d8547cea99bde8e44d58dd15e](https://github.com/discourse/discourse-data-explorer/commit/316923f1906d811d8547cea99bde8e44d58dd15e)
**_When the limit is reached, the UI indicates that only partial results are shown:_**
* * *
**_When the limit is not reached, the exact number of results is shown:_**
---
### Author:  [@rishabh](https://meta.discourse.org/u/rishabh)
#### Post date: [September 18, 2018, 6:27pm UTC](https://meta.discourse.org/t/have-data-explorer-show-number-of-results/96687/13 "2018-09-18T18:27:11Z")
> [@Mittineague](#):
>
> **COUNT** (users.username) **OVER** **() FROM** users
Thanks for that tip, OVER() is a pretty cool function!
It works awesome but hiding the **count** column with the same identical values for every row was somewhat complicated.
---
### Author:  [@sam](https://meta.discourse.org/u/sam)
#### Post date: [November 3, 2020, 7:02am UTC](https://meta.discourse.org/t/have-data-explorer-show-number-of-results/96687/14 "2020-11-03T07:02:46Z")
We are showing counts now, closing this 🙂
---
### Author:  [@sam](https://meta.discourse.org/u/sam)
#### Post date: [November 3, 2020, 7:02am UTC](https://meta.discourse.org/t/have-data-explorer-show-number-of-results/96687/15 "2020-11-03T07:02:48Z")