SQL - Trouble counting values-CodePudding

I have a Database named 'IDMS' and it has two columns named 'Table' and 'Column'. I Need to do a simple query, list the values in 'Column' with a count of duplicates.

I tried this

Select `'Colum', Count(*)
From IDMS
GROUP by 'Column';`

and not joy. I have tried differing variations of that select and it's not working.

Can anyone assist?

-Ron

CodePudding user response：

I'm not sure if I've understood your question but if you use reserved names you can just put it []

If you need to know the column/table duplicated, had a the column [table] and if you need to filter it you can just use having like @mimi said

Select 
[Column], 
Count(*),
CASE WHEN Count(*) > 1 THEN 'Yes' ELSE 'NO' END as IsDuplicated 
From IDMS
GROUP by [Column];

CodePudding user response：

First, I believe you should remove the quotes (') around your "Column" column (I assume its a column from your table and you dont want a fixed 'Column' value as return). Also there is a spelling difference between your first Colum, and second ColumN values; so I will asume first field is named Column1 and second, Column2.

Second, to add the required condition to your query you should use HAVING clause with the desired requirement. For example, if you want result that have more than 1 occurrence of Column2 column in your table:

SELECT Column1, COUNT(Column2)
FROM IDMS
GROUP BY Column1
HAVING COUNT(Column2) > 1;