Count all rows where a field value only appears once

[Adamhumbug]

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"

 

[Barand]

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?

[requinix]

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.