how do i combine two selects from one table where one has a GROUP BY and the other has a DISTINCT

Question

Both of these work individually

SELECT  PONumber, 
        count(RequestNumber) as "InNumOfRequests", 
        sum(Amount) as "SumofAmounts" 
FROM tableView
GROUP BY PONumber  

SELECT DISTINCT PONumber, 
                (10 * ceiling((DATEDIFF(day,POApprovedDate,GETDATE()))/10)) 
                  AS "BINofDaysSincePOApproved"                      
FROM tableView

And I want to end up with:

PONumber   |  InNumOfRequests  |  SumofAmounts  |  BINofDaysSincePOApproved  
PO1        |  2                |  100           |  180
PO2        |  1                |  50            |  179   

tableView looks like:

RequestNumber | PONumber | Amount  |  POApproved Date   
1             | PO1      | 100.00  |  2010-01-01  
2             | PO1      | 100.00  |  2010-01-01  
3             | PO2      |  50.00  |  2010-01-02

note that PO1 is actually the PO for 2 requests and so the POApproved Data and Amount is the same for those 2 requests.

It seems easy but from the book I'm using (The Language of SQL) i can't figure it out.
Help :(
Alex

Answer 1

Hello!

SELECT *
   FROM (
         SELECT PONumber, count(RequestNumber) as "InNumOfRequests", sum(Amount) as "SumofAmounts"
           FROM tableView
          GROUP BY PONumber
        ) a,
        (
         SELECT DISTINCT PONumber, (10 * ceiling((DATEDIFF(day, POApprovedDate, GETDATE()))/10)) AS "BINofDaysSincePOApproved"
           FROM tableView
        ) b
 WHERE a.PONumber = b.PONumber

Answer 2

If the date is ALWAYS equal to each PONumber, can be put in the first select. The MIN function (MIN(POApprovedDate)) is why a group by, then should use aggregate functions in the fields.

SELECT  PONumber, 
        count(RequestNumber) as "InNumOfRequests", 
        sum(Amount) as "SumofAmounts",
        (10 * ceiling((DATEDIFF(day,MIN(POApprovedDate),GETDATE()))/10)) 
         AS "BINofDaysSincePOApproved" 
FROM tableView
GROUP BY PONumber