I have table in DB like this (ID column is not a unique UUID, just some object ID, primary key still exists, but removed for example)

I want to get a some kind of statistic report like

Is it possible in some way? UNION isn't working were, clause GROUP BY also have no idea how can solve that

I can do it easily in kotlin, just request everything and aggregate to classes like that

    data class SearchAggregate (
        val id: String,
        val options: List<Option>,
        val values: List<Value>
    )
    
    data class Option (
        val name: String,
        val totalSearches: Long
    )

    data class Value(
        val name: String,
        val totalSearches: Long
    )

and export to file but I have to request data by SQL

CodePudding user response：

You can use the COUNT() window function in a subquery to preprocess the data. For example:

select
  id,
  max(total_searches) as total_searches,
  option,
  max(total_options) as total_options,
  value,
  max(total_values) as total_values
from (
  select
    id,
    count(*) over(partition by id) as total_searches,
    option,
    count(*) over(partition by id, option) as total_options,
    value,
    count(*) over(partition by id, option, value) as total_values
  from t
) x
group by id, option, value

See running example at DB Fiddle #1.

Or you can use a shorter query, as in:

select
  id,
  sum(cnt) over(partition by id) as total_searches,
  option,
  sum(cnt) over(partition by id, option) as total_searches,
  value,
  cnt
from (
  select id, option, value, count(*) as cnt from t group by id, option, value
) x

See running example at DB Fiddle #2.

CodePudding user response：

The first option is to use ROLLUP, as that is the intended SQL pattern. It doesn't give you the results in the format you asked for. That's a reflection on the format you asked for not being normalised.

SELECT
  id,
  option,
  value,
  SUM(`Number of searches`)   AS total_searches
FROM
  your_table
GROUP BY
  ROLLUP(
    id,
    option,
    value
  )

It's concise, standard practice, SQL Friendly, etc, etc.

Thinking in terms of these normalised patterns will make your use of SQL much more effective.

That said, you CAN use SQL to aggregate and restructure the results. You get the structure you want, but with more code and increased maintenance, lower flexibility, etc.

SELECT
  id,
  SUM(SUM(`Number of searches`)) OVER (PARTITION BY id)   as total_by_id,
  option,
  SUM(SUM(`Number of searches`)) OVER (PARTITION BY id, option)   as total_by_id_option,
  value,
  SUM(`Number of searches`)   AS total_by_id_option_value
FROM
  your_table
GROUP BY
  id,
  option,
  value

That doesn't leave blanks where you have them, but that's because to do so is a SQL Anti-Pattern, and should be handled in your presentation layer, not in the database.

Oh, and please don't use column names with spaces; stick to alphanumeric characters with underscores.

Demo : https://www.db-fiddle.com/f/fX3tNL82gqgVCRoa3v6snP/5