0

I have a complex query and fetches rows of textual data. This part works fine. I would like to include a count of rows returned, so that exported spreadsheet is easier to filter.

So assuming we have a query of this sort:

SELECT
    TRIM(COL_1 || ' (' || COL_2 || ')') AS MY_DATA
FROM
    TABLE_1
    INNER JOIN TABLE_2...
WHERE
    TABLE_1.COL_1=...
ORDER BY
    TABLE_1.COL_1

I can do:

SELECT * FROM (quoted query)

and it gives me the result set.

I can do: 

SELECT MY_DATA FROM (quoted query)

and it gives me the result set.

I can do: 

SELECT COUNT(MY_DATA) FROM (quoted query)

and it gives me the row count.

But when I try: 

SELECT COUNT(MY_DATA), MY-DATA FROM (quoted query)

I get

ORA-00937: not a single-group group function

Using Oracle 11, can I select both the row count and row details from a subselect? The subselect in my case is complex and I do not want to duplicate it to get count and details separately. I tried WITH/AS but ran into the same error.

====

Note: this is not a duplicate of suggested question - I do not use any grouping in my case. It's not a grouping issue - just a misleading error message.

user6651485
  • 89
  • 1
  • 9
  • This question is not a duplicate of the referenced question. This question asks about returning the count with every detail record. – DCookie Oct 19 '16 at 14:08

1 Answers1

2

You want an analytic function here:

SELECT COUNT(MY_DATA) OVER () ct, MY-DATA FROM (quoted query)

This will give you the count of the total number of records returned by the select in each row returned.

DCookie
  • 42,630
  • 11
  • 83
  • 92
  • @user6651485 - You could also do a cross join (Cartesian join) between `select count(*)...` and `select my_data...`, and the join wouldn't be expensive since the left-hand side has only one row. But this would still require going over the result set of your query twice, once to get the count and then again to do the join. This is the great benefit of analytic functions, and why DCookie's answer is the right one. –  Oct 19 '16 at 14:09
  • Works like a charm. Thanks! – user6651485 Oct 19 '16 at 14:15