Code Style of COUNT(), AVG() and other aggregations

Answered

Hey,

I was editing my Code Style through the preferences, but I'm missing options for aggregation functions like COUNT() and AVG().

I can not choose to have them always as capital letters the same as keywords like SELECT and FROM. 

Additionally, I can't specifically say when to start new lines for these functions or when not. For example, I'm struggling on how to stop the reformat to change 

COUNT(DISTINCT user_id) AS exposed_users

into 

COUNT(
DISTINCT user_id) AS exposed_users

Is there an option I'm missing? Can you let me know how to improve the coding style?

Thank you!

4 comments
Comment actions Permalink

Hello, Stefan!

1.

> to have them always as capital letters the same as keywords like SELECT and FROM

Do you mean to get other functions in lower case but these two in CAPS? Why?

We have no such feature, you're welcome to file an issue into our issue tracker, but please add some words where such style is useful :)

 

2.

Line break after COUNT( — looks like a bug or misconfiguration.

Please double check that the dialect is specified correctly. Which dialect do you use?

0
Comment actions Permalink

I use PostgreSQL dialect.

1.
I want all SQL specific words to be capital letters. These include all keywords like SELECT and FROM, but also functions like COUNT and AVG. I want all of them as capital letters, not only these 2 examples I've given. 

Here is the window in the preferences in DataGrip where it lets you choose the Word Case of different groups of words. count(), avg() and similar functions are not influenced by any of these options. 

Example on how I'd like it with all SQL words capitalised:
SELECT activity_date, COUNT(*)
FROM usertable
WHERE country = 'United States'

After testing with other dialects, it seems PostgreSQL doesn't recognise count() as a keyword, doesn't color it orange like the other keywords and thus doesn't change them to upper. In other dialects it's changed to upper (MySQL, Amazon Redshift,..)

 

2.
I use PostgreSQL dialect, but this behaviour happens in all dialects. It always adds a line break after COUNT(
Going through the Code Style options again and adding the code I see this behaviour in into the window, I can't find any option that changes this behaviour. But it happens when automatically reformatting.

0
Comment actions Permalink

Hello Stefan

 

1.

I've filed a feature request https://youtrack.jetbrains.com/issue/DBE-10985 — you're welcome to watch/vote for.

 

2.

I couldn't reproduce the case, please describe how to reproduce.

0
Comment actions Permalink

Okay, I now found out why it happens:

We use specific strings we will replace in a future step in the programming (= %1$s, %2$s). DataGrip doesn't know what to do with them, and the code breaks afterwards, breaking the formatting and the recognition of Keywords. This thread is related to that: https://intellij-support.jetbrains.com/hc/en-us/community/posts/360008537320-Stop-reformatting-of-specific-strings 

example code:

SELECT
activity_date,
users.language,
%1$s
count(*) AS impressions,
count(DISTINCT impressions.user_id) AS exposed_users
FROM
impressions
JOIN users
on impressions.user_id = users.user_id
GROUP BY
1, 2 %2$s
0

Please sign in to leave a comment.