CODEWITHSUNDEEP
sqljoinsqlite
CodeFusionMobile
CodeFusionMobile

Reputation: 15110

How to join SQL tables while selecting a COUNT() function?

I have two database tables, 'Lists' and 'Notes'.
Lists has columns _id, listname
Notes has columns _id, checked, list_id (which is a foreign key of Lists._id), and more columns that aren't relevant to this question.

I would like to create a query that returns four columns:
Lists._id, Lists.listname, the count of all checked Notes in this list, the count of all Notes in this list.

The query should return all entries from Lists.

I can get the counts and the _ids & listnames seperately, using the following queries

SELECT _id, listname FROM Lists
SELECT count(checked) FROM Notes WHERE checked='1' and list_id=foo_id
SELECT count(*) FROM Notes WHERE list_id=foo_id

In these statements, foo_id refers to the _id of the list I would like to count checked and total items for.

Could someone show me how I could put these all into one query?

Bonus points for throwing an AS in there for the count columns.

Upvotes: 2

Views: 4451

Answers (2)

Peter Lang
Peter Lang

Reputation: 55524

EDIT: Added IFNULL to return 0 as count_checked when outer join returns no rows; added aliases.

SELECT
  l._id,
  l.listname,
  IFNULL( SUM(n.checked), 0 ) AS count_checked,
  COUNT(*) AS count_total
FROM lists l
LEFT OUTER JOIN notes n ON ( l._id = n.list_id )
GROUP BY l._id, l.listname

Upvotes: 8

Ray
Ray

Reputation: 21905

SELECT _id, listname, 
    (SELECT count(checked) FROM Notes WHERE checked='1' and Notes.list_id=Lists.list_id ) AS count_checked,
    (SELECT count(*) FROM Notes WHERE Notes.list_id=Lists.list_id) as count_all
  FROM Lists

Upvotes: 1

Related Questions