Re: help with SQL join - Mailing list pgsql-general

> -----Original Message-----
> From: John R Pierce [mailto:pierce@hogranch.com]
> Sent: Thursday, February 11, 2010 3:01 PM
> To: pgsql-general@postgresql.org
> Subject: Re: help with SQL join
>
> Neil Stlyz wrote:
> > 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?
>
> something like...
>
> SELECT results.customerid, c.customername, count(distinct
> count1) AS "TODAYS_USERS", count(distinct count2) AS "LAST 7
> DAYS" , count(distinct count3) AS "LAST 30 DAYS"
>     FROM (SELECT distinct case when s.modified >=
> '2010-02-11' then s.modelnumber else null end as count1,
>        case when s.modified >= '2010-02-04' then
> s.modelnumber else null end as count2,
>        case when s.modified >= '2010-01-11' then
> s.modelnumber else null end as count3, s.customerid
>            FROM sales as s WHERE s.modelnumber LIKE 'GH77%')
> AS results
>     JOIN customers as c ON (results.customerid = c.customerid)
>     GROUP BY results.customerid
>


One correction:  you should "group" on all non-aggregate columns in your
"select" list, i.e.:

 SELECT results.customerid, c.customername, count(distinct
 count1) AS "TODAYS_USERS", count(distinct count2) AS "LAST 7
 DAYS" , count(distinct count3) AS "LAST 30 DAYS"
     FROM (SELECT distinct case when s.modified >=
 '2010-02-11' then s.modelnumber else null end as count1,
        case when s.modified >= '2010-02-04' then
 s.modelnumber else null end as count2,
        case when s.modified >= '2010-01-11' then
 s.modelnumber else null end as count3, s.customerid
            FROM sales as s WHERE s.modelnumber LIKE 'GH77%')
 AS results
     JOIN customers as c ON (results.customerid = c.customerid)
     GROUP BY results.customerid, c.customername

Igor Neyman