Jump to content
Adamhumbug

Count all rows where a field value only appears once

Recommended Posts

I am trying to count all of the rows that have a unique value in a field (meaning that it only appears once)

I have a select that selects the rows but i would like this to be a count of the number of rows primerily

Secondly i would like to turn this into a delete statement. (i thought i could change select to delete but it appears this is not correct)

 

SELECT line_number,logon_time,product_id,transaction_product_qty,transaction_status, MIN(transaction_id) as transaction_id
FROM transaction_log
GROUP BY transaction_id
HAVING COUNT(*) = 1"

 

Share this post


Link to post
Share on other sites
6 minutes ago, Adamhumbug said:

line_number,logon_time,product_id,transaction_product_qty,transaction_status

As you are grouping by transaction_id, all the values in those columns are meaningless.

What is the table structue?

In which column are looking for unique values?

What purpose is the MIN() serving?

Share this post


Link to post
Share on other sites

You want a count of transaction_ids that only have one row each? One inner query that lists all the transaction_ids with one row, then an outer query to count the number of rows inside.

To delete, think about what you could do with that same subquery and an IN.

2 hours ago, Barand said:

As you are grouping by transaction_id, all the values in those columns are meaningless.

Ah, but it's restricted to cases where there's only one row per transaction_id. No ambiguity so it works out.

 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.