Alex Rivera | Logout

Multiple counts within a single SQL query

Asked 2009-08-13T01:53:09.017
12

I'm trying to get the count of documents within 4 specific sections using the following code:

SELECT
    category.id
    , category.title
    , count(ts1.section_id) AS doc1
    , count(ts2.section_id) AS doc2
    , count(ts3.section_id) AS doc3
    , count(ts4.section_id) AS doc4
FROM
    category 
    LEFT JOIN category_link_section AS ts1
        ON (category.id = ts1.category_id AND ts1.section_id = 1)
    LEFT JOIN category_link_section AS ts2
        ON (category.id = ts2.category_id AND ts2.section_id = 2)
    LEFT JOIN category_link_section AS ts3
        ON (category.id = ts3.category_id AND ts3.section_id = 3)
    LEFT JOIN category_link_section AS ts4
        ON (category.id = ts4.category_id AND ts4.section_id = 4)
GROUP BY category.id, ts1.section_id, ts2.section_id, ts3.section_id, ts4.section_id

The table 'category' had an id, title etc. The table 'category_link_section' contains id linkages between category_id, section_id, and doc_id.

If the count is 0 for any column, it displays 0 in that column. But if the result is not 0 it shows the multiplication result of all the section results. So if my 4 count columns were supposed to return: 1, 2, 0, 3; it would actually show 6, 6, 0, 6;

If I use this following code for each specific category I get the results I want:

SELECT
    category.id
    , category.title
    , count(ts1.section_id) AS doc1
FROM
    category 
    LEFT JOIN category_link_section AS ts1
        ON (category.id = ts1.category_id AND ts1.section_id = 1)
GROUP BY category.id, ts1.section_id

but I then need to cycle through the database each time for each section.

So my question is, do I need to step through and call each section in turn, constructing my table outside the SQL, or can this be done in a single query?

Edit
Report

1 Answer

6

You might want to try something like this:

SELECT
    category.id
    , category.title
    , SUM(IF(ts1.section_id = 1, 1, 0)) AS doc1
    , SUM(IF(ts1.section_id = 2, 1, 0)) AS doc2
    , SUM(IF(ts1.section_id = 3, 1, 0)) AS doc3
    , SUM(IF(ts1.section_id = 4, 1, 0)) AS doc4
FROM
    category 
    LEFT JOIN category_link_section AS ts1
        ON (category.id = ts1.category_id AND ts1.section_id = 1)
GROUP BY category.id, ts1.section_id
answered 2009-08-13T02:05:55.710

Your Answer