1

Topic: Subquery in from or case?

Problem in request. It is necessary to deduce kol-in victories of each pair during the project.
Fought long, to begin and could not. Thought to do through CASE or to do the new table...
Operating time were in the plan

SELECT ID_para, SUM (ball) result;
case result when = then ' 1_win ' else ' lose ' end comment and  
FROM sostav_sudi s JOIN vistuplenie v ON s.id_vistyplenia=v.id
WHERE dtvist = "2017-10-03"
GROUP BY ID_para

Whether At once there is a request it is possible to do case in case?
Intuitively understanding that the given construction will be big, besides it is necessary to invent further count _wins, becomes ripe a question, whether there is such possibility as in  languages, to fulfill a cycle with count in a variable. Since  in books and Google, clarified only presence if constructions and not necessary ..
Tables
[spoiler] [img=https://cdn1.savepice.ru/uploads/2017/10/17/b2797684d73d1185ac350c617b3a9eb4-full.png]
Performance
[img=https://cdn1.savepice.ru/uploads/2017/11/14/476257a3cfcb93efec216f4d67fb5122-full.png]
Composition of judges
[img=https://cdn1.savepice.ru/uploads/2017/11/14/282e94b53808c264b961242a7e733da6-full.png] [/spoiler]

2

Re: Subquery in from or case?

Thought longer, solved so.

SELECT id_para, SUM (ball) as b1, id_vistyplenia, dtvist
FROM sostav_sudi s JOIN vistuplenie v ON s.id_vistyplenia=v.id
GROUP BY 3
HAVING SUM (ball)>
ANY (SELECT SUM (ball)
FROM sostav_sudi s JOIN vistuplenie v ON s.id_vistyplenia=v.id WHERE dtvist = "2017-10-01"
GROUP BY id_vistyplenia)

Received each pair which has typed  ball in any competitive bottom.
[img=https://cdn1.savepice.ru/uploads/2017/11/14/24a80c1dcda9934405fc0ac393d9f69a-full.png]
Now an output problem, kol-in victories at each pair. But here an ill luck, at us here a drawn game on second of November.
I want to organize it a case. But something produces an error
[spoiler]

SELECT id_para, COUNT (b1)
,case WHEN b1=b1 WHEN "no wins" else "win" end q1
FROM (SELECT id_para, SUM (ball) as b1, id_vistyplenia, dtvist
FROM sostav_sudi s JOIN vistuplenie v ON s.id_vistyplenia=v.id
GROUP BY 3
HAVING SUM (ball)>
ANY (SELECT SUM (ball)
FROM sostav_sudi s JOIN vistuplenie v ON s.id_vistyplenia=v.id WHERE dtvist = "2017-10-01"
GROUP BY id_vistyplenia)) as q1

[/spoiler]

3

Re: Subquery in from or case?

Whether

noobforsql wrote:

it is possible to do case in case?

It is possible. But what for?

4

Re: Subquery in from or case?

Akina, I decided to do by a subquery. Stuck here on an output kol-va (

5

Re: Subquery in from or case?

noobforsql , such impression that you ask us to be glad (or to sympathize)...
On one screenshots and the left unfinished requests, without made DDL and the full setting of the task, I think, anybody to help you especially and does not undertake.

6

Re: Subquery in from or case?

noobforsql;
It is possible to do enclosed Case expressions.

7

Re: Subquery in from or case?

It is impossible to do cycles, requests not on it.
On setting it agree with before. The orator.