CODEWITHSUNDEEP
sqlpostgresql
Dan Ciborowski - MSFT
Dan Ciborowski - MSFT

Reputation: 7207

Merging three queries into single results

I have three queries that all return 1 row and 1 column. I would like to return this as 1 row, and three columns.

My queries

select count(distinct rt) from gpstable where rt >  GETDATE() - '1 day'::INTERVAL AND di like 'i';
select count(distinct rt) as count from gpstable where rt >  GETDATE() - '1 day'::INTERVAL group by di order by count limit 1;
select count(distinct rt) as count from gpstable where rt >  GETDATE() - '1 day'::INTERVAL group by di order by count DESC limit 1;

This is what I have tried

select userCount, min, max 
from 
(select count(distinct rt) as userCount from gpstablev2 where rt >  GETDATE() - '1 day'::INTERVAL AND di like 'UmTqUo1MQU8FgXfXXGNFh7vZnQN+bt2ThIQIDHNnmWU=') as userCount,
(select count(distinct rt) as min from gpstablev2 where rt >  GETDATE() - '1 day'::INTERVAL group by di order by count limit 1) as min,
(select count(distinct rt) as max from gpstablev2 where rt >  GETDATE() - '1 day'::INTERVAL group by di order by count DESC limit 1) as max;

And I get the following error ERROR: column "count" does not exist in gpstablev2 [SQL State=42703]

Upvotes: 0

Views: 45

Answers (1)

user330315
user330315

Reputation:

You were close, you just need to put the individual statements into the select list, not the from:

select (select count(distinct rt) from gpstable where rt >  GETDATE() - '1 day'::INTERVAL AND di = 'i') as user_count,
       (select count(distinct rt) from gpstable where rt >  GETDATE() - '1 day'::INTERVAL group by di order by count limit 1) as min_count,
       (select count(distinct rt) from gpstable where rt >  GETDATE() - '1 day'::INTERVAL group by di order by count DESC limit 1) as max_ount;

But I think this can be simplified and reduced to a single query over the table:

select max(case when di = 'i' then di_count end) as user_count, 
       min(di_count) as min_count,
       max(di_count) as max_count
from (
    select di, 
           count(distinct rt) as di_count
    from gpstable 
    where rt >  current_date - interval '1' day
    group by di
)t ;

This only needs to go through the table once, instead of three times as in your attempt. That will be much more efficient. I only tested this on a very tiny dataset, so it might be that I missed something.

I used the ANSI SQL format to specify an interval interval '1' day because I prefer standard syntax whenever there is an equivalent version (that's also why I used current_date instead of getdate(). For the duration of "one day" this wouldn't actually be necessary, because the default unit is a day, so current_date - 1 would work just as well.

Upvotes: 3

Related Questions