Re: [GENERAL] help with SQL join
> -Original Message- > From: John R Pierce [mailto:pie...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] help with SQL join
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
