StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POselect SQL troubles
text
Body
copied!<p>I have a SQL query as follows:</p> <pre><code> select a.class_id, b.std_id, ( select count(*) from enrollment where class_id = a.class_id ) as class_size, d.admission_id from class a, student b, enrollment c, admission d where c.class_id = a.class_id and c.std_id = d.std_id and c.std_id = b.std_id order by a.class_id; </code></pre> <p>The result is:</p> <pre><code>class_id std_id class_size comp100 8080 4 comp100 8020 4 comp100 8033 4 comp100 8111 4 comp305 8080 4 comp305 8080 4 comp305 8020 4 comp305 8033 4 comp555 8111 1 comp672 8080 3 comp672 8033 3 comp672 8111 3 </code></pre> <p>But I just want to display one of the results if the class size is more than 1</p> <p>that is</p> <p>I would like to do so:</p> <pre><code>class_id std_id class_size comp100 8080 4 comp305 8080 4 comp555 8111 1 comp672 8080 3 </code></pre> <p>After thinking a while, I should get the counter first, like this</p> <pre><code>select enrollment.class_id, count(*) as class_size from class, enrollment where enrollment.class_id = class.class_id group by enrollment.class_id; </code></pre> <p>then the counter is retrieved as follows:</p> <pre><code>class_id class_size comp100 4 comp305 4 comp555 1 comp672 3 </code></pre> <p>however, I don't know how to use one SQL statement to realize the result.</p> <p>I would like to ask for your advice, how to modify the SQL statement so that I can show only one of the rows if the class size is more than 1. </p> <p>that is, if the class size is 4, i do not want to show 4 results, instead, i result is enough.</p> <p>Thank you very much!</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

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