SELECT expression to count elements

Question

I have an SQL table consisting of:

[title]
ElementA
ElementA
ElementB
ElementC
ElementA
ElementA

I am trying to find a way to count the element that is most found; in this example, it will be 4because it ElementAhappened 4 times. The problem is that this table is dynamic, so I can’t just saySELECT COUNT(*) WHERE title = 'ElementA';

Does anyone know how to write instructions SELECTfor this? Conceptually, this seems pretty simple, but I just can't get it to work.

Thanks a lot, Brett

Answer 1

SELECT TOP 1 Title, COUNT(*) FROM table GROUP BY Title ORDER BY 2 DESC

or

SELECT Title, COUNT(*) FROM table GROUP BY Title ORDER BY 2 DESC LIMIT = 1

depending on the product you are using.

(Edited to correct the ORDER BY clause).

Answer 2

Select TOP 1 Title

FROM


(

Select 
   Count(title) k, title
FROM
    titles
GROUP BY 
   TITLE

) Count

 ORDER BY
      k Desc

Answer 3

,

SELECT Title, Count (*) as NumOccurences
FROM MyTable
GROUP BY Title
Order BY NumOccurences

, SQL

SELECT TOP 1 Title, NumOccurences
FROM 
(
    SELECT Title, Count (*) NumOccurences
    FROM MyTable
    GROUP BY Title
    Order BY Count (*) DESC
) AS Titles

Answer 4

Sql:

SELECT TOP 1 
    Title, COUNT(*) AS Count
FROM tbl
GROUP BY Title
ORDER BY Count DESC

Postgresql, MySQL ..:

SELECT 
    Title, COUNT(*) AS Count
FROM tbl
GROUP BY Title
ORDER BY Count DESC
LIMIT 1

Answer 5

Use a group to group items followed by the maximum value on the corresponding counter. Use nested queries to simplify the solution .. :)

Answer 6

WITH CTE (Title, Counter) as
 (SELECT Title, Count(*) Counter FROM #Test GROUP BY Title) 

SELECT Top 1 Title, Counter FROM CTE order by Counter Desc