Обсуждение: 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
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
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
Neil
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
> -----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