Create table which shows earliest and latest date for same names(IDs)

So from my table where I have columns: name, date

I would like to select names that repeat point out how many times they repeat and then from the rows with same names I want to print out earlies and latest date for that name.

After all my table would have columns: name, how many, earliest date, latest date

SELECT 
    name, 
    COUNT(name) AS ilosc, 
    MIN(createdDate) as earliest date, 
    MAX(createdDate) AS latest date
FROM Clients
GROUP BY name
HAVING COUNT(name)>1
WHERE name IS NOT NULL;

I am not sure though if MIN and MAX functions will take into consideration values only from places with the same name or they will take into consideration all the fields.

Do I need to add additional WHERE statements for MIN and MAX functions so they only choose fields with the same names?

sample database that I will be using is:

(NULL,'20160101 20:36:12'),
('MARK','20160101 20:38:27'),
('BOB','20160101 20:39:02'),
('MARK','20160101 20:41:55'),
('MARK','20160101 20:42:31'),
('MARK','20160101 20:51:11'),
('MARK','20160101 20:51:24'),
(NULL,'20160101 20:52:01'),
('BOB','20160101 20:53:07'),
('BOB','20160101 20:53:44'),
('KATE','20160101 20:54:52'),
(NULL,'20160101 20:54:57'),
('JOEL','20160101 20:54:59'),
(NULL,'20160101 20:55:03'),
(NULL,'20160101 20:56:12'),
('KATE','20160101 20:57:22'),
('JOEL','20160101 20:58:31'),
('JOEL','20160101 20:59:41'),
('JOEL','20160101 21:02:02'),
('JOEL','20160101 21:03:59'),
('MARK','20160101 21:42:31'),
('MARK','20160101 21:51:11'),
('MARK','20160101 21:51:24')

Answer

First, the correctly formed query looks like:

SELECT name, 
       COUNT(*) AS ilosc, 
       MIN(createdDate) as earliest_date, 
       MAX(createdDate) as latest_date
FROM Klienci
WHERE name IS NOT NULL;
GROUP BY name
HAVING COUNT(*) > 1;

Notes:

• The WHERE clause goes immediately after the FROM clause. The ordering of clauses is important.
• Column aliases cannot have spaces unless you wrap them in escape characters. Just use an underscore.
• Although count(name) is fine, count(*) is more convenient for just counting rows.

As for your question: The aggregation functions only return values based on rows that match the group. If you wanted overall numbers, you could use window functions in combination with them:

SELECT name, 
       COUNT(*) AS ilosc, 
       MIN(createdDate) as earliest_date, 
       MAX(createdDate) as latest_date,
       SUM(COUNT(*)) OVER () as total_count,
       MIN(MIN(createdDate)) OVER () as oeverall_earlest_date,
       MAX(MAX(createdDate)) OVER () as oeverall_latest_date
FROM Klienci
WHERE name IS NOT NULL;
GROUP BY name
HAVING COUNT(*) > 1;

Note that the “overall” numbers are only for the rows that are not filtered out by the WHERE and HAVING clauses.