[Solved] Query the number of Duplicates per item in a table

[securityrunsblue]

Happy Holidays,
I'm trying to learn Databases so please go easy on me,,,, I have a large db that I'm attempting to query to see how many recurrences there are for tickets coming in so I can start creating SOP / KB's for the most frequent ones. These are system generated event tickets so the Summary title is standardized for the most part. I was able to convert the CSV to ODS and import it into Base with no problem but when I try to query it with the wizard I'm not seeing a way to pull what I'm needing. I am trying to learn so if there is a query you can share that would do this or any other way besides the wizard I'd appreciate it, I'm sorry if this is such a basic question,,, I've searched online and haven't been able to find a suitable answer yet. If anyone knows how to do this I'd appreciate the help. Thanks!

[F3K Total]

Hello,

let's say you have a table DATA, and a Column TEXT.
You now want to know, how many recurrences of each text are in table DATA, then the following query should do the job.

Code: Select all

SELECT "TEXT", COUNT( * ) AS "Recurrences" FROM "DATA" GROUP BY "TEXT" ORDER BY "Recurrences" DESC

For entering the query, change to Queries in your Basedocument, then Create Query in SQL View..., see picture

Copy/Paste or type in the Queries-SQL-Code above.
Save it, e.g. as qRecurrences, close it, an doubleclick the Query.
Here's the result, on the left the table DATA, on the right the Query qRecurrences

find attached a sample-file
R

[securityrunsblue]

Thanks,, this was exactly what I needed!