simplify sql query Oracle database

Question

Task: Write a query that returns a list of houses from the TB_ELEKTROSTAL_2018 table. It is considered that all subscribers who have the same address belong to one house, with the exception of the apartment number. Group the list of houses by street. For each house, indicate the number of apartments; for each street, add a final line indicating the number of houses on the street. Also, for each street, add a line indicating the number of multi-storey buildings. We will consider a multi-storey building a building in which the number of apartments exceeds the number 90.

Table structure:
TB_ELEKTROSTAL_2018
Dom|typstr(street type (avenue, boulevard))| namestr|dom(house)|owner|kwa(flat)

Solution:

SELECT typstr, namestr, dom, to_char(count(UNIQUE kwa)) AS flats_count FROM tb_elektrostal_2018 GROUP BY typstr, namestr, dom
UNION ALL
SELECT typstr, namestr, 'Total house: '||to_char(count(UNIQUE dom)) AS dom, '-' AS flats_count FROM tb_elektrostal_2018 GROUP BY typstr, namestr
UNION ALL
SELECT typstr, namestr, 'Total multi-storey buildings: '||(SELECT count(*) FROM(SELECT dom FROM tb_elektrostal_2018 tb2 WHERE tb1.namestr = tb2.namestr GROUP BY dom HAVING count(UNIQUE kwa) > 90)) AS dom, '-' AS flats_count 
FROM tb_elektrostal_2018 tb1 GROUP BY typstr, namestr
ORDER BY typstr, namestr, dom

Is there a better way to write a query? Thanks in advance for your reply

Answer 1

Your prefixing the output of each row type with a different identifying string indicates that the result set rows do not all "mean the same thing" and thus, although all sourced from the same table, that you are effectively merging 3 conceptually different queries into a single "report" query.

Merging the 3 queries into a single one dilutes the result "meaning" and makes the result set of the query messy for any purpose other than a static report.

Its potential advantage is performance, as instead of running 3 separate queries - and thus accessing the SQL engine 3 times - you are only accessing it once. I am not sure of the size of your data set so it is difficult to ascertain the requirement for this.

As mentioned this answer is largely conceptual as without more insight into your data set it is difficult to make any clear statement. However performance aside, my preferred approach would be to split the query into 3 independent queries where each result set reflects its true meaning (and thus provides data alone without requiring string prefixes injected to indicate meaning)

I hope this proves useful.