Databases
StackOverflow2013
Postsdbo
POMySQL - count different fields on different conditions in one joined table
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMySQL - count different fields on different conditions in one joined table
    primarykey
    data
    text
    <p>There is a hypothetical table called Table1, with the columns:</p> <ul> <li>id</li> <li>condition 1</li> <li>condition 2</li> <li>joinable_key</li> </ul> <p>There is another one, Main_Table, whose ID corresponds with the joinable_key in the first one.</p> <p>I would like to join them in such a fashion that I can count the rows of Table1 according to both condition 1 and condition 2 separately - that is, I would like to be able to perform "count(Table1.condition1) as first_condition, count(Table1.condtion2) as second_condition" on the select query.</p> <p>The query would presumably look something like this:</p> <pre><code>SELECT Main_Table.some_column, COUNT(Table1.condition1) AS first_condition, COUNT(Table1.condtion2) AS second_condition FROM Main_Table LEFT JOIN Table1 AS T1 on (T1.joinable_key = Main_Table.id AND T1.condition1 = 'something') LEFT JOIN Table1 AS T2 on (T2.joinable_key = Main_Table.id AND T2.condition2 = 'something else') GROUP BY (Main_Table.id) </code></pre> <p>When this executes, however, both count results are equal, and actually multiply with each other. It is imperative that all results be included in the final output - including those that do not have any entries from Table1 - that is, if there is no row in Table1 with a joinable_key equal to Main_Table.id, it too needs to be included.</p> <p>Before anyone suggest actually doing two separate queries and handling it through PHP - yes, I know why and how it can be done, but my goal is to find out whether or not this multi-count can be done all in one query.</p> <p>Thank you</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USQuassnoi
    1. USSwader
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POMySQL - count different fields on different conditions in one joined table
      1. This table or related slice is empty.
      1. VTUpMod
    1. COpossible duplicate of [Counting multiple rows in MySQL in one query](http://stackoverflow.com/questions/2957269/counting-multiple-rows-in-mysql-in-one-query)
      singulars
      1. POMySQL - count different fields on different conditions in one joined table
      1. USKonerak
 

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