CODEWITHSUNDEEP
sqlitecount
maperz
maperz

Reputation: 196

SQLite: Two different Counts on different table

I am pretty sure you guys have a simple and fast solution to my problem but my SQL-Skills are limited and I can't figure it out by my self.

So what I have is something like:

Newsgroup: [Name(PK)]

Article: [Id(PK)] [newsgroupName (FK)] [date:Date] [read:Boolean]

What I want know is a query that gives me the Name of each Newsgroup along with the Count of unread articles, Count of all articles and the date of the most recent one... Is this even possible to archieve in a single Select-Query?

Thank you guys in advance!

Upvotes: 0

Views: 48

Answers (2)

CL.
CL.

Reputation: 180162

You can simply use the appropriate aggregation functions:

SELECT newsgroupName,
       SUM(NOT read) AS countUnread,
       COUNT(*)      AS countAll,
       MAX(date)     AS mostRecentDate
FROM Article
GROUP BY newsgroupName;

Upvotes: 2

Mauro Piccotti
Mauro Piccotti

Reputation: 1847

I guess something like this:

SELECT name, COUNT(countUnread) AS countUnread, COUNT(countRead) AS countRead, MAX(mostRecentDateUnread) AS mostRecentDateUnread, MAX(mostRecentDateRead) AS mostRecentDateRead FROM (
    SELECT newsgroupName AS name, COUNT(newsgroupName) AS countUnread, 0 AS countRead, MAX(date) AS mostRecentDateUnread, NULL AS mostRecentDateRead
    FROM Article
    WHERE read = 0
    GROUP BY newsgroupName, read

    UNION ALL

    SELECT newsgroupName AS name, 0 AS countUnread, COUNT(newsgroupName) AS countRead, NULL AS mostRecentDateUnread, MAX(date) AS mostRecentDateRead
    FROM Article
    WHERE read = 1
    GROUP BY newsgroupName, read
)
GROUP BY name

I haven't tried but in theory with some fix it could work.

Upvotes: 0

Related Questions