How to use case expression and min() with group by

Question

The following query when I that execute

SELECT St.FirstName,St.LastName,
       CASE
         WHEN St.ISPackage='N' THEN Min(St.VisitingDate)
         WHEN St.ISPackage='Y' THEN St.VisitingDate
       END AS VisitingDate
  FROM SalesTransaction As St
  (...inner join and where clause)
  GROUP BY St.FirstName,St.LastName, St.VisitingDate

If I use St.VisitingDate in Group By the result is duplicated.

If not used St.VisitingDate in Group By show error

invalid in the select list because it is not contained in either an aggregate function

How can I solve this problem?

Answer 1

I suspect that you want to group by name and get the visit date when ISPackage is "Y", and fallback on the earliest visit date if there is no date where ISPackage is "Y".

If so, you can do:

SELECT St.FirstName,St.LastName,
    COALESCE(MIN(CASE WHEN St.ISPackage = 'Y' THEN St.VisitingDate END), MIN(St.VisitingDate)) VisitingDate
FROM SalesTransaction As St
-- (...inner join and where clause)
GROUP BY St.FirstName,St.LastName

Answer 2

you can use below SQL- I calculated min date using a subquery.

SELECT St.FirstName,St.LastName,
       CASE
         WHEN St.ISPackage='Y' THEN St.VisitingDate
         ELSE min_dt.min_VisitingDate
       END AS VisitingDate
  FROM 
  SalesTransaction As St
  (...inner join and where clause)
left join (select St.FirstName,St.LastName, min(St.VisitingDate) min_VisitingDate from SalesTransaction group by St.FirstName,St.LastName) min_dt 
ON St.FirstName =min_dt.FirstName ,St.LastName =min_dt.LastName