Apply aggregate function to certain columns using SQL in Apache Druid

user_13253119 0
Viewed 532

I have a table like this

----------------------
code    sales    goal
----------------------
  b       7       20
  b      12       20
  a       9       15
  c       4        3
  a       4       15

And I want to perform an agg function to group by a sum only on 'sales' column, because 'goal' column is common to a given value in 'code' column to get something more like this

---------------------------------
code    total    goal
---------------------------------
  b       19      20
  a       13      15
  c        4       3

Is there a way to perform something like this?

SELECT code, SUM(sales) AS total, goal FROM such_table GROUP BY code

To then operate the columns to achieve the following future operations:

------------------------------------------------------
code    sum(sales)   intact(goal)  achvd(100*sum/goal)
------------------------------------------------------
  b         19           20               95
  a         13           15               86.6
  c          4            3              133.3
druid aggregate-functions group-by sql
2 Answers

Either include goal in the group by:

SELECT code, SUM(sales) AS total, goal, SUM(sales) * 1.0 / goal as ratio
FROM such_table
GROUP BY code, goal;

Or make it the argument to an aggregation function:

SELECT code, SUM(sales) AS total, MAX(goal) as goal,
       SUM(sales) * 1.0 / MAX(goal) as ratio
FROM such_table
GROUP BY code;

If your data is actually structured this way, you have a problem with your data model. The goal should not be repeated on each row. You should have one table with one row per code and that table should have the goal. Then this table can just contain the sales.

user_11440350

Thank you all who contributed to this answer, I wanted to post the solution I use by reading you and surfing the web.
Given the table such_table: 

----------------------
code    sales    goal
----------------------
  b       7       20
  b      12       20
  a       9       15
  b       2       20
  c       4        3
  a       4       15

When applying the following query: 

SELECT
  code,
  SUM(sales) AS total,
  goal
FROM such_table
GROUP BY code, goal;

Results in the next table:

---------------------
code    total    goal
---------------------
  b      19       20
  a      13       15
  c       4        3

Thus in order to display desired results, next query is used: 

SELECT
  code,
  SUM(sales) AS total,
  goal,
  100 * (SUM(sales) / goal) AS prc_of_goal
FROM such_table
GROUP BY code, goal;

Resulting in the following:

-----------------------------------
code    total    goal   prc_of_goal
-----------------------------------
  b      19       20       95
  a      13       15       86.67
  c       4        3      133.33

Which is what I was looking for
Thank you all
:D

user_132531190
Related