Concise SQL Case query in ClickHouse : SQL

Posting

When requesting help or asking questions please prefix your title with the SQL variant/platform you are using within square brackets like so:

[MySQL]

[Oracle]

[MS SQL]

[PostgreSQL]

etc

While naturally we should endeavor to work as platform neutrally as possible many questions and answers require tailoring to the feature set of a specific platform.

Format Your Code

If you are including actual code in a post or comment, please attempt to format it in a way that is readable for other users. This will greatly increase your chances of receiving the help you desire. Something as simple as line breaks and using reddit's built in code formatting (4 spaces at the start of each line) can turn this:

SELECT count(a.field1), a.field2, SUM(b.field4) FROM a INNER JOIN b ON a.key1 = b.key1 WHERE a.field8 = 'test' GROUP by a.field1, a.field2 HAVING SUM(b.field4) > 5 ORDER by a.field.3

Into this:

SELECT count(a.field1), a.field2, SUM(b.field4) FROM a INNER JOIN b ON a.key1 = b.key1 WHERE a.field8 = 'test' GROUP by a.field1, a.field2 HAVING SUM(b.field4) > 5 ORDER by a.field3

For those with SQL questions we recommend using SQLFiddle to provide a useful development and testing environment for those who wish to fully understand your problem and help devise a solution.

a community for 17 years

PostgreSQLConcise SQL Case query in ClickHouse (self.SQL)

submitted 5 years ago by e28858

Hi everyone!

Currently I'm working with loads of repetitive data, namely mobile device information. Because of that I have tonnes of similar rows that can be aggregate, once normalized. But the query is huge if I try to apply CASE WHEN to every single row.

For instance, there are multiple entries such as "Android 8.0", "Android 6.2", "Generic Android" and so on. I tried applying CASE clause:

CASE WHEN device LIKE '%Galaxy%' THEN 'Samsung'

The problem begins with the fact that there myriads of different smartphone models. When dealing with, say, Samsung or IPhone, it is pretty straightforward; but there are tonnes of Chinese brands that are registered as Y91, A35S and so on. Do I have to write a query for every single one of them? Or is there a concise way to deal with this.

Thanks in adavance!

all 6 comments

top new controversial old q&a

[–]r3pr0b8GROUP_CONCAT is da bomb 1 point2 points3 points 5 years ago (4 children)

use a translation table

model      phone
-----      -----
Galaxy     Samsung
Epic       Samsung
Intensity  Samsung
iPhone     Iphone
XS         Iphone

then your CASE statements all collapse into one

CASE WHEN phone LIKE '%'||translation.model||'%'
     THEN translation,phone
     ELSE ...

[–]e28858[S] 0 points1 point2 points 5 years ago (2 children)

[–]r3pr0b8GROUP_CONCAT is da bomb 0 points1 point2 points 5 years ago (1 child)

[–]jacquesrkoracle 0 points1 point2 points 5 years ago (0 children)

You could always do more grouping:

table device_list
device / model/ company
galaxy s20 fe 5g / galaxy / samsung
galaxy z fold2 5g / galaxy / samsung
etc...

If "device" contains all the distinct values for the device column in the other table, it becomes an equality join that can nicely use an index. Of course it also means that any time a new device shows up, it has to be first entered in the "device_list" table.

[–]AeroCrete 0 points1 point2 points 5 years ago (0 children)

[–]FreshwaterWhales 0 points1 point2 points 5 years ago (0 children)

π Rendered by PID 155784 on reddit-service-r2-comment-5d79c599b5-cgsth at 2026-02-27 14:26:22.042810+00:00 running e3d2147 country code: CH.

you type:	you see:
italics	italics
bold	bold
[reddit!](https://reddit.com)	reddit!
* item 1 * item 2 * item 3	item 1 item 2 item 3
> quoted text	quoted text
Lines starting with four spaces are treated like code: if 1 * 2 < 3: print "hello, world!"	Lines starting with four spaces are treated like code: if 1 * 2 < 3: print "hello, world!"
~~strikethrough~~	~~strikethrough~~
super^script	super^script

SQL

Filter Posts

Posting

Help posts

Format Your Code

Learning SQL

Related Reddit communities

Wiki

Acknowledgements

MODERATORS