Count on the same table

Question

Hi,

i have got a problem with my SQL Statement:

Table structure:

CREATE TABLE "tags" (
  "id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  "page_id" INTEGER NOT NULL,
  "title" TEXT NOT NULL
);

Now i want to select all the tags from one page and the frequency of this tag in the whole database. I have the following SQL statement:

SELECT title, title as t,
(SELECT COUNT(*) FROM tags WHERE title=t) as count
FROM tags WHERE page_id=42 ORDER BY count DESC, title
COLLATE NOCASE ASC;

But the error message i'm getting from SQLite3 Driver in PHP:

no such column: t

How do i have to write the SQL statement so that i get the result i want to have?

Thanks, Levu

Answer 1

do you mean like this...

SELECT title, COUNT(*) as freq
FROM tags
WHERE page_id=42
GROUP BY title
ORDER BY freq DESC, title

Answer 2

select title, count(*) as frequency
from tags
where page_id = 42
group by title
order by count(*) desc

If you want tags appear more than one

select title, count(*) as frequency
from tags
where page_id = 42
group by title
having count(*) > 1
order by count(*) desc

Answer 3

Try this one

SELECT title, 
(SELECT COUNT(*) FROM tags WHERE title=t.title) as count
FROM tags t 
WHERE page_id=42 
ORDER BY count DESC, title
COLLATE NOCASE ASC;