Databases
StackOverflow2013
Postsdbo
POMultiple counts within a single SQL query
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMultiple counts within a single SQL query
    primarykey
    data
    text
    <p>I'm trying to get the count of documents within 4 specific sections using the following code:</p> <pre><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 </code></pre> <p>The table 'category' had an id, title etc. The table 'category_link_section' contains id linkages between category_id, section_id, and doc_id.</p> <p>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; </p> <p>If I use this following code for each specific category I get the results I want:</p> <pre><code>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 </code></pre> <p>but I then need to cycle through the database each time for each section.</p> <p>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?</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USDas123
    plurals
    1. This table or related slice is empty.
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POMultiple counts within a single SQL query
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POMultiple counts within a single SQL query
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POMultiple counts within a single SQL query
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload