Search Postgresql Archives

help with SQL join

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hello,
 
I need help with the an advanced SQL JOIN. I think that I can get this accomplished with a LEFT JOIN... perhaps some of you may be able to offer guidance in this... I appreciate any suggestions you may have.
 
 
Consider the following SQL statement:
 
 
SELECT customerid, count(disctint count1) AS "TODAYS_USERS",
count(distinct count2) AS "LAST 7 DAYS" ,
count(distinct count3) AS "LAST 30 DAYS" FROM
(SELECT distinct case when modified >= '2010-02-11' then modelnumber else null end as count1,
   case when modified >= '2010-02-04' then modelnumber else null end as count2,
   case when modified >= '2010-01-11' then modelnumber else null end as count3, customerid
   FROM sales WHERE modelnumber LIKE 'GH77%')
   AS results GROUP BY results.customerid
 
 
Now, the above SQL query retrieves results that will look something like this:
 
 
 customerid    |  TODAYS_USERS   |   LAST 7 DAYS    |   LAST 30 DAYS
 bigint            |  bigint                         |   bigint                    |   bigint
----------------------------------------------------------------------
 8699         |  1                                  |   1                          |   1
 8700         |  1                                  |   12                        |   17
 8701         |  3                                  |   5                          |   19


Now... here is the problem I am having... the above SQL query is retrieving results from one table: sales
I have another table called customers with a couple of fields (customerid, and customername are two of the fields).
I want to join on the customerid in both tables to retrieve the customername in the query.
So I need the results to look something like this:
 
customerid        |        customername        |        TODAYS_USERS        |        LAST 7 DAYS        |        LAST 30 DAYS
bigint                |        varchar                   |        bigint                              |        bigint                        |        bigint
--------------------------------------------------------------------------------------------------------------------------------
8699                |         Joe Smith              |        1                                    |        1                               |        1
8700                |         Sara Olson            |        1                                    |        12                             |        17
8701                |         Mike Jones           |        3                                     |        5                               |       19
 
Can someone show me how to use a JOIN with the above SQL Statement? I need to bring the customername field into the query from the other table and I have been having issues writting the query... can this even be done?
 
Thanks,
Neil
 


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux