CODEWITHSUNDEEP
mysqlpostgresql
TheLostMind
TheLostMind

Reputation: 36304

Postgres - Count() based on multiple columns

I have a table : device

----------------------------------------------------------------
| device__some_id   |   device__os_name  |  device__os_version |
 ---------------------------------------------------------------
|       0                   android               3.0            |

|       0                   android               3.0            |

|       0                   android               4.0            |

|       1                   ios                   8.0            |

|________________________________________________________________|

I have a query which returns count(*) for each tenant,os_name, os_version combination. output :

--------------------------------------------------------------------------
| device__some_id   |   device__os_name  |  device__os_version | Count |
 -------------------------------------------------------------------------
        0                    android               3.0               2
        0                    android               4.0               1
        1                    ios                   8.0               1

Query used :

SELECT f,p,q,count (*)
FROM (SELECT  device__some_id as f, device__os_name as p, device__os_version as q FROM device) as foo
GROUP BY f,p,q

Issue :

I have another table dim_os which has the following fields :

 | dim_os__some_id   |   dim_os__os_type  |  dim_os__os_version |
     ------------------------------------------------------------
            1                 android               3.0               
            2                 android               4.0              
            3                 ios                   8.0

WHat I want to do is just replace each occurance of device_os_name and device_os_version in the output of first query by the some_id from the dim_os table.

My try : 

SELECT f, p, count (*)
FROM (SELECT  dev.device__some_id as f, j2.dim_os__some_id as p  FROM device AS dev 
INNER JOIN dim_os as j1 ON dev.device__os_name = j1.dim_os__os_type 
INNER JOIN dim_os as j2 ON dev.device__os_version = j2.dim_os__os_version) as foo
GROUP BY f,p ;

WHich doesn't give me proper counts. Where am I going wrong?

Upvotes: 0

Views: 1942

Answers (1)

Raphaël Althaus
Raphaël Althaus

Reputation: 60493

Well, not sure why you do two inner join, I would try with one (with two conditions).

But I'm not absolutely sure I understood your desired output !

Anyway :

SELECT f, p, count (*)
FROM 
     (SELECT  
       dev.device__some_id as f, 
       j1.dim_os__some_id as p  
      FROM device AS dev 
      INNER JOIN dim_os as j1 
          ON dev.device__os_name = j1.dim_os__os_type and                
             dev.device__os_version = j1.dim_os__os_version) as foo
GROUP BY f,p ;

see SqlFiddle

Upvotes: 1

Related Questions