CODEWITHSUNDEEP
sqlsql-servert-sql
cspell
cspell

Reputation: 181

How to use Count with Max function and Group By

I'm using this query below to get a list of stale accounts but I wanted to try and get a count of the number of rows returned instead of a full listing but am stuck & looking for help.

SELECT customer_name, MAX(order_date) AS last_Sale_Date
FROM sales_history
GROUP BY customer_name
HAVING max(order_date) <= '01/01/2017 23:59:59'

Upvotes: 1

Views: 59

Answers (3)

KeithL
KeithL

Reputation: 5594

I'll put this up there as an alternative and likely the way I would have written it:

select count(*)
from (select customer_name, max(order_Date) LastOrder
      from sales_history
      group by customer_name) LastOrders --Everyones last order
where LastOrder < '2018-01-02'

Results in a count of customers that haven't ordered since 1/2/2018.

Upvotes: 0

Lukasz Szozda
Lukasz Szozda

Reputation: 175656

You could use:

SELECT TOP 1 COUNT(*) OVER()
FROM sales_history
GROUP BY customer_name
HAVING max(order_date) <='01/01/2017 23:59:59';

DBFiddle Demo

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269703

Use a subquery:

SELECT COUNT(*)
FROM (SELECT customer_name, MAX(order_date) AS last_Sale_Date
      FROM sales_history
      GROUP BY customer_name
      HAVING max(order_date) < '2018-01-02'
     ) sh

Notice that I changed the date format in the HAVING clause.

Upvotes: 0

Related Questions