COUNT(*) gives the total number of records in the table including null values. COUNT(1) gives the total number of records in the table including null values. COUNT(column_name) only considers rows where the column contains a non-NULL value.
SQL SELECT COUNT() can be clubbed with SQL WHERE clause. Using the WHERE clause, we have access to restrict the data to be fed to the COUNT() function and SELECT statement through a condition.
To count the number of rows, use the id column which stores unique values (in our example we use COUNT(id) ). Next, use the GROUP BY clause to group records according to columns (the GROUP BY category above). After using GROUP BY to filter records with aggregate functions like COUNT, use the HAVING clause.
Use the HAVING clause and GROUP By the fields that make the row unique
The below will find
all users that have more than one payment per day with the same account number
SELECT
user_id ,
COUNT(*) count
FROM
PAYMENT
GROUP BY
account,
user_id ,
date
HAVING
COUNT(*) > 1
Update If you want to only include those that have a distinct ZIP you can get a distinct set first and then perform you HAVING/GROUP BY
SELECT
user_id,
account_no ,
date,
COUNT(*)
FROM
(SELECT DISTINCT
user_id,
account_no ,
zip,
date
FROM
payment
)
payment
GROUP BY
user_id,
account_no ,
date
HAVING COUNT(*) > 1
Try this query:
SELECT column_name
FROM table_name
GROUP BY column_name
HAVING COUNT(column_name) = 1;
I wouldn't recommend the HAVING
keyword for newbies, it is essentially for legacy purposes.
I am not clear on what is the key for this table (is it fully normalized, I wonder?), consequently I find it difficult to follow your specification:
I would like to find all records for all users that have more than one payment per day with the same account number... Additionally, there should be a filter than only counts the records whose ZIP code is different.
So I've taken a literal interpretation.
The following is more verbose but could be easier to understand and therefore maintain (I've used a CTE for the table PAYMENT_TALLIES
but it could be a VIEW
:
WITH PAYMENT_TALLIES (user_id, zip, tally)
AS
(
SELECT user_id, zip, COUNT(*) AS tally
FROM PAYMENT
GROUP
BY user_id, zip
)
SELECT DISTINCT *
FROM PAYMENT AS P
WHERE EXISTS (
SELECT *
FROM PAYMENT_TALLIES AS PT
WHERE P.user_id = PT.user_id
AND PT.tally > 1
);
create table payment(
user_id int(11),
account int(11) not null,
zip int(11) not null,
dt date not null
);
insert into payment values
(1,123,55555,'2009-12-12'),
(1,123,66666,'2009-12-12'),
(1,123,77777,'2009-12-13'),
(2,456,77777,'2009-12-14'),
(2,456,77777,'2009-12-14'),
(2,789,77777,'2009-12-14'),
(2,789,77777,'2009-12-14');
select foo.user_id, foo.cnt from
(select user_id,count(account) as cnt, dt from payment group by account, dt) foo
where foo.cnt > 1;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With