How to group by multiple values?


#1

Hello, I’m quite new to Redash and I’ve been trying to find a way to visualize a query, where I use multiple group by’s e.g.

SELECT date, count(players)
FROM inapppurchase
WHERE date = ‘{{ date }}’
GROUP BY date, platform, gameversion, usertype

I would like the visualization to make separate lines for example based on both platform & usertype so lines would be:

  • Android guest,
  • Android registered,
  • iOS guest,
  • iOS registered.

I can get it to work on the table, but not in a graph.

I would also like to know how to use parameters with 0, 1 or multiple values? Let’s say as an example I want to keep my query on default showing the sum of all platforms, but I would like to have a parameter where I can tell the query to show

  • sum of all platforms (empty parameter)
  • only Android (parameter: Android)
  • both Android and iOS (parameter: Android, iOS)

Are these things possible?
Thank you for your time


#2

Hi @M_Salenius!

Hope I understood your questions correctly :sweat_smile:

Try these solutions (probably you’ll need to combine/modify them to fit your needs, it’s just an example):

  1. Modify your query to concat paltform and user type into single field - it should become something like (PostgreSQL syntax):
SELECT date, platform || ' ' || usertype as "series", count(players)
FROM inapppurchase
WHERE date = ‘{{ date }}’
GROUP BY date, 2

Then use “Group By” chart option - select that concatenated value there; it will create series based on unique values from that field.

  1. Use parameter with predefined set of values and WHERE clause.

Parameter values:

'*'
'Android'
'Android', 'iOS'

In SQL:

SELECT *
FROM inapppurchase
WHERE 
  {param} = '*' -- TRUE if '*' selected - match all records
  OR platform IN ({param}) -- this will work if other value selected
  -- other WHERE statements

#3

Thank you I got it working somewhat, the solution just seems unnecessary complex for something this simple. Is there a specific reason why it’s not allowed to group by multiple values in Redash by default?


#4

At this point I’m not sure if it was some limitation of the charting library we were using or just an oversight when implementing. It’s a reasonable feature to have.


#5

Yeah it feels like extremely important feature for proper analysis as I need to be able to combine different groupings flexibly while trying to look answers for specific issues or trends in the graph and this concate is really stiff for that work. I pretty much have to hard code all the combinations before starting my analysis. My measurements have on average ~10 different fields which I need to be able to combine on the fly. Hope you can do something about it soon :slight_smile: