0

I need to write a query to display the customer code, customer first name, last name, full address, invoice date, and invoice total of the largest purchase made by each customer in Alabama (including any customers in Alabama who have never made a purchase; their invoice dates should be NULL and the invoice totals should display as 0).

Here is an ERD of the two required tables.

Here is what my latest query looks like.

select distinct lgcustomer.cust_code
      ,Cust_FName
      ,Cust_LName
      ,Cust_Street
      ,Cust_City
      ,Cust_State
      ,Cust_ZIP
      ,max(inv_total) as [Largest Invoice]
      ,inv_date
from lgcustomer left join lginvoice on lgcustomer.cust_code = lginvoice.cust_code
where Cust_State = 'AL'
group by lgcustomer.cust_code
      ,Cust_FName
      ,Cust_Lname
      ,Cust_Street
      ,Cust_City
      ,Cust_State
      ,Cust_ZIP
      ,Inv_Date

I don't understand why, despite using DISTINCT lgcustomer.cust_code as well as only the MAX(inv_total), it still returns every inv_total for that customer.

My professor says to make use of UNION, but as I understand it, that is for compiling two different tables with the same attributes...

I appreciate any responses that can point me in the right direction!

Solution

The answer we came to in class was to use a correlated subquery and union.

select c.cust_code
      ,cust_fname
      ,cust_lname
      ,cust_street
      ,cust_city
      ,cust_state
      ,inv_date
      ,inv_total
from lgcustomer c left outer join lginvoice i on c.cust_code = i.cust_code
where cust_state = 'AL'
and inv_total = (select max(inv_total)
                 from lginvoice i2
                 where i2.cust_code = c.cust_code)

union
select c.cust_code
      ,cust_fname
      ,cust_lname
      ,cust_street
      ,cust_city
      ,cust_state
      ,''
      ,0

from lgcustomer c left outer join lginvoice i on c.cust_code = i.cust_code
where cust_state = 'AL'
and inv_date is null
and inv_total is null
order by cust_lname asc
Gerald
  • 521
  • 1
  • 6
  • 16
  • Your query does not return every `inv_total` for that customer, only the totals for each day when the customers ordered something. If you drop the date from `GROUP BY` and the select list, you'd get one line per customer. – Sergey Kalinichenko Nov 12 '13 at 18:06
  • Great! I guess my next question would be how to add the date that the max invoice occurred, if it cannot be included in the group by. – Gerald Nov 12 '13 at 18:25
  • There are several ways of doing it. Could you paste your query into the question, because it's hard to type it in from an image? Make sure that there's a blank line on each side of the pasted SQL, then select the pasted query, and click the `{}` button in the editor. This will put the correct formatting. – Sergey Kalinichenko Nov 12 '13 at 18:31
  • Similar question answered here. Link --> [Select first row in each GROUP BY group?](http://stackoverflow.com/questions/3800551/select-first-row-in-each-group-by-group) – tokafew420 Nov 12 '13 at 18:38
  • You tagged it greatest-n-per-group. That'S a well known term and there are well-known solutions like OUTER APPLY and ROW_NUMBER. – usr Nov 12 '13 at 19:36
  • I didn't apply that tag. Bill Karwin did. – Gerald Nov 12 '13 at 20:28

0 Answers0