Databases
StackOverflow2013
Postsdbo
PORewrite as one mySQL query where results of 1st query needed for 2nd query
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PORewrite as one mySQL query where results of 1st query needed for 2nd query
    primarykey
    data
    text
    <p>I have a mySQL query similar to this:</p> <pre><code>SELECT c1, c2 FROM a WHERE a.c3 = 123 </code></pre> <p>I have a second query that I need to execute for each value of c2 like this:</p> <pre><code>SELECT COUNT(*) AS `Counter` FROM a WHERE a.c2 = [each value of a.c2] </code></pre> <p>At the moment, I programmatically loop through each result from the first query and perform the second query. So, what's happening is that the main query uses a specific value of c3 to generate a result set. Then, the second query uses that result set and does the COUNT for each returned value of c2 without any limitation on values for c3.</p> <p>Is it possible to rewrite the first query so that it also returns the aggregate results for <code>Counter</code>? In other words, I want to end up with a result set which includes <code>c1, c2, Counter</code>. </p> <p>I tried writing a subquery, but this doesn't work:</p> <pre><code>SELECT c1, c2, (SELECT COUNT(*) AS `Counter` FROM a) FROM a WHERE a.c3 = 123 </code></pre> <p>(I don't know how to make the WHERE clause use the retrieved values from the "main" query.)</p> <p>I'm working with an older application which is using mySQL 4.1.</p> <p>EDIT to show data and desired result:</p> <p>Assume that table <code>a</code> looks as follows:</p> <pre> | c1 | c2 | |+++++++++++| | 222 | 101 | | 223 | 101 | | 224 | 101 | | 222 | 102 | | 223 | 102 | </pre> <p>Given a specified value for c2, I find all the matching values of c1. So, if <code>c2=101</code>:</p> <pre> | c1 | |+++++| | 222 | | 223 | | 224 | </pre> <p>Now, for each of these 3 values, I want to <code>Count</code> the number of times the value appears in the full table <code>a</code>:</p> <pre> | c1 | count | |+++++++++++++| | 222 | 2 | | 223 | 2 | | 224 | 1 | </pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USjalperin
    1. USjalperin
    plurals
    1. This table or related slice is empty.
    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
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. PORewrite as one mySQL query where results of 1st query needed for 2nd query
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PORewrite as one mySQL query where results of 1st query needed for 2nd query
      1. This table or related slice is empty.
      1. VTUpMod
    1. CONot exactly sure what you are looking for results... Can you show some sample data that show the repeated rows / values, and what you EXPECT based on the sample data as a return set.
      singulars
      1. PORewrite as one mySQL query where results of 1st query needed for 2nd query
      1. USDRapp
    2. COthanks for the suggestion. added.
      singulars
      1. PORewrite as one mySQL query where results of 1st query needed for 2nd query
      1. USjalperin
 

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