How can I join these tables in postgresql?

Question

I have a query like this:

select *, (CAST (ie_usage_count as float)/total_count)*100 as percent_ie from(

SELECT DISTINCT CAST (account_id AS bigint),
    count(case when 
            user_agent LIKE '%MSIE 7%'
            AND user_agent NOT LIKE '%Trident%' 
            then 1 end) as ie_usage_count,
    count(*) as total_usage
FROM acc_logs
WHERE account_id NOT LIKE 'Account ID '
group by account_id
ORDER BY account_id )
where not ie_usage_count = 0

That gives me a table with account_ids, and the ie_usage_count, total_usage, and percent_ie associated with each account ID

account_id | ie_usage_count | total_usage | percent_ie

I have another query

select name, account_id
from accounts

That gives me the name of the person associated with each account.

name | account_id |

I'd like to have a single query that includes name, account_id, ie_usage_count, total_usage, and percent_ie.

name | account_id | ie_usage_count | total_usage | percent_ie

Any ideas?

Gordon Linoff · Accepted Answer · 2014-08-01T19:18:32.170

Your first query is more easily written as:

select CAST(account_id AS bigint),
       SUM(case when  user_agent LIKE '%MSIE 7%' AND user_agent NOT LIKE '%Trident%' 
                then 1 else 0
            end) as ie_usage_count,
       count(*) as total_usage,
       AVG(case when  user_agent LIKE '%MSIE 7%' AND user_agent NOT LIKE '%Trident%' 
                then 100.0 else 0
            end) as percent_ie
from acc_logs
where account_id NOT LIKE 'Account ID '
group by account_id
having SUM(case when  user_agent LIKE '%MSIE 7%' AND user_agent NOT LIKE '%Trident%' 
                then 1 else 0
            end) <> 0;

You can get the name just by joining it in:

select CAST(al.account_id AS bigint), a.name,
       SUM(case when user_agent LIKE '%MSIE 7%' AND user_agent NOT LIKE '%Trident%' 
                then 1 else 0
            end) as ie_usage_count,
       count(*) as total_usage,
       AVG(case when  user_agent LIKE '%MSIE 7%' AND user_agent NOT LIKE '%Trident%' 
                then 100.0 else 0
            end) as percent_ie
from acc_logs al join
     accounts a
     on al.account_id = a.account_id
where al.account_id NOT LIKE 'Account ID '
group by al.account_id, a.name
having SUM(case when  user_agent LIKE '%MSIE 7%' AND user_agent NOT LIKE '%Trident%' 
                then 1 else 0
            end) <> 0;

I get ERROR: column al.name does not exist – johncorser Aug 01 '14 at 19:14 — johncorser, Aug 01 '14 at 19:14

xQbert · Answer 2 · 2014-08-01T19:16:12.140

UNTESTED but perhaps something simple like a join, add on account_ID and then addition of a group by... but this makes several assumptions .. like acc_Logs has all accounts, or you only want percent_IE when there is a log entry... if no acc_Log or no account then no record...

select *, (CAST (ie_usage_count as float)/total_count)*100 as percent_ie from(

SELECT DISTINCT CAST (B.account_id AS bigint),
    count(case when 
            user_agent LIKE '%MSIE 7%'
            AND user_agent NOT LIKE '%Trident%' 
            then 1 end) as ie_usage_count,
    count(*) as total_usage,
    A.name
FROM acc_logs B
INNER JOIN Accounts A
 on A.Account_ID = B.account_ID
WHERE B.account_id NOT LIKE 'Account ID '
group by B.account_id, A.Name
ORDER BY B.account_id )
where not ie_usage_count = 0

added alias to tables.

I get ERROR: column reference "account_id" is ambiguous – johncorser Aug 01 '14 at 19:14 — johncorser, Aug 01 '14 at 19:14

score 0 · Answer 3 · edited May 23 '17 at 12:04

Just join to it:

SELECT a.name, l.*, (l.ie_usage_count * 100)::float / l.total_count AS percent_ie
FROM  (
   SELECT account_id::bigint       -- Why cast to bigint?
        , count(user_agent LIKE '%MSIE 7%'
            AND user_agent NOT LIKE '%Trident%' 
            OR NULL) AS ie_usage_count
        , count(*) AS total_usage
   FROM   acc_logs
   WHERE  account_id NOT LIKE 'Account ID '  -- trailing blank? typo?
   GROUP  BY account_id
   ORDER  BY account_id
   ) l
JOIN  accounts a USING (account_id)
WHERE ie_usage_count <> 0;

Your original query was invalid, because the subquery had no alias.
Otherwise, the subquery is just fine, don't flatten it. Short syntax, percentage calculation and join to accounts only for qualifying rows, division by 0 avoided elegantly.
Why do you cast account_id to bigint? There's something off here. Are you storing numbers as text? I would need to see your table definitions ...
Reformulated the calculation. It's cheaper and more precise to multiply the integer by 100 before you cast to float.
You may want to cast to numeric instead and wrap that into round(expression, 2) to get pretty output.
Using a shorter (equivalent) expression for the conditional count. Details here.
Simplified your WHERE condition.

How can I join these tables in postgresql?

3 Answers3