0

My query SELECTs 1 row from table1, which has a number of fields plus one very long text field. It JOINs with a second table2, that may have many matching corresponding rows. I would like to be able to spread the long text field, from table1, out over multiple lines (the number determined by the matching rows in table2).

enter image description here

In the table above there is one row from Table1 where the key is table1_key = 'DU' and year = '2017'. Then there are four rows from Table2 with matching keys that include the programme_code and course_code. The field long_text_field, which I want to spread over multiple lines, comes from Table1.

Is it possible to extract a total row count for records that have been selected for output and still output the rows? Meaning that I want to see a count of 4 from the above and be able to work with this number in the displayed output. So that the result looks something like this:

enter image description here

  • 1
    Can you please edit your question with sample input and output data? – Philᵀᴹ Oct 18 at 8:31
0

I don't get your example, but if that's just an illustration of the question in the heading, the answer is a window function:

SELECT /* all your columns and expressions */,
       count(*) OVER () AS total_count
FROM /* the rest of your query */

This will append a column to each result row with the total number of result rows.

The window function is called after GROUP BY, but before ORDER BY and LIMIT.

  • Thanks for this Laurenz - sorry about the delay in responding. Window Functions - they look like they can solve this problem and some others that I am facing now. Thanks again. – Paul Pritchard Nov 13 at 5:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.