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

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...

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.