#### Topic: Subquery with sorting

Hello. I study SQL independently. Invented a problem. How it can be implemented theoretically? Here such trouble. We admit, it is necessary to implement the report in a type to the table. A vertical column are written  pupils, and in a horizontal column of their estimation. It is necessary to deduce on intersection kol-in estimations.
It will be better to drive on SELECT COUNT (*) to each cell or somehow it is possible to use the temporary table?

#### Re: Subquery with sorting

It is necessary to read up the textbook to slander GROUP BY and all rises on the places.

#### Re: Subquery with sorting

If estimations a limited number, for example it is possible to esteem 1,2,3,4,5 that still about iif, case and their usage with count.

#### Re: Subquery with sorting

Ivan_Pisarevsky;
Thanks, turn out, something like it? ( the person vertically, and kol-in pencils on colors it is horizontal in the table)?
SELECT t1.,
(SELECT COUNT (*) FROM table2 as t2 WHERE (t2.type=1) and (t1.=t2.)) AS ' an amount red ',
(SELECT COUNT (*) FROM table2 as t3 WHERE (t3.type=2) and (t1.=t3.)) AS ' an amount dark blue'
FROM table2 as t1
GROUP BY t1.

#### Re: Subquery with sorting

Rphoenix;
It is possible and so, but will be faster

``````SELECT t1. "Person";
sum (iif (t2.type = 1, 1, 0)) "an amount red";
sum (iif (t2.type = 2, 1, 0)) "an amount dark blue"
FROM table2 t1
join table2 t2 on t1. "Person" = t2. "Person"
GROUP BY 1``````

#### Re: Subquery with sorting

Shavljuk Evgenie;
Thanks! And if it is necessary not all people, but only defined? The additional table is necessary?

#### Re: Subquery with sorting

Rphoenix wrote:

It is necessary to deduce on intersection kol-in estimations.

``select fio, ocenka, count (*) from table``

And further already know stuff numbers on intersections of a line from the first field with a column from
The second. The trivial task in any programming language.

#### Re: Subquery with sorting

Shavljuk Evgenie, writes Dynamic SQL Error.

#### Re: Subquery with sorting

Rphoenix wrote:

Dimitry Sibiryakov;
That is to create a heap

``````select count (*) from spisok as s
where s.ocenka=2 and s.fio like ' Ivanov'
into:COUNT1;
select count (*) from spisok as s
where s.ocenka=3 and s.fio like ' Ivanov'
into:COUNT2;
select count (*) from spisok as s
where s.ocenka=4 and s.fio like ' Ivanov'
into:COUNT3;
select count (*) from spisok as s
where s.ocenka=5 and s.fio like ' Ivanov'
into:COUNT4;
select count (*) from spisok as s
where s.ocenka=2 and s.fio like ' Petrov'
into:COUNT5;
Etc.?``````

#### Re: Subquery with sorting

Rphoenix wrote:

that is to create a heap

I do not booze. The One and only request which selects all estimations of all necessary people. And this
entirely I wrote request above. The filtration on names is necessary - add
Appropriate condition, but anything else do not touch.

#### Re: Subquery with sorting

Dimitry Sibiryakov wrote:

And  ** I wrote above.

And is not present, not entirely. Forgot "group by FIO, ocenka".

#### Re: Subquery with sorting

Dimitry Sibiryakov;
c in a condition

``Where ((ocenka=2 and fio =') or (ocenka=3 and fio =') or (ocenka=3 and fio =') or (ocenka=4 and fio =') or (ocenka=5 and fio =')) into:COUNT1)``

?

#### Re: Subquery with sorting

Solved with the help

``For select``

#### Re: Subquery with sorting

thinking at you  not programmer, but any deputy
To you speak - select BASIC where, and from it tear out logic the necessary conditions
Instead of "one million where with same if"
also to  in cycles we reach...

#### Re: Subquery with sorting

Evgenie wrote:

``````SELECT t1. "Person";
sum (iif (t2.type = 1, 1, 0)) "an amount red";
sum (iif (t2.type = 2, 1, 0)) "an amount dark blue"
FROM table2 t1
join table2 t2 on t1. "Person" = t2. "Person"
GROUP BY 1``````

Irritates already this ubiquitous IIF. I will be in the bad mood - I will start to clean such "councils".
Write on standard CASE, do not hesitate.

#### Re: Subquery with sorting

Rphoenix wrote:

I Study SQL independently.

it is possible to learn under what textbook? As a variant to take Martin Grabera's creation, the link is in the stuck subject.

#### Re: Subquery with sorting

WildSery;
And than it is bad IIF? To me it is pleasant at least to that record is more compact
As on me,

``iif (t2.type = 1, 1, 0)``

looks better than

``case when t2.type = 1 then 1 else 0 end``

#### Re: Subquery with sorting

Evgenie wrote:

WildSery;
And than it is bad IIF? To me it is pleasant at least to that record is more compact
As on me,

``iif (t2.type = 1, 1, 0)``

looks better than

``case when t2.type = 1 then 1 else 0 end``

decode  on 1 character is shorter

``````sum (iif (t2.type = 2, 1, 0)) "an amount dark blue"
sum (decode (t2.type, 1, 0)) "an amount dark blue"``````

But iif is more evident.

#### Re: Subquery with sorting

afgm wrote:

But iif is more evident.

case the most clear.
In this case and "else 0" it is possible not to write.

#### Re: Subquery with sorting

Ivan_Pisarevsky wrote:

it is passed...
case the most clear.

case who except English is more clear to that than any languages does not know, and no docks has.
Its minus in that that many superfluous words.
If to write to single line - that is rather badly visible to the logician.
If to space apart for some lines - that request blurs in height and it becomes badly visible to the logician of request.
In this case iif is the most pertinent.
But as soon as condition variants there is more than 1-2 that iif there and then merges and forward escapes case .

Ivan_Pisarevsky wrote:

In this case and "else 0" it is possible not to write.

Variants with "not to write" - they do not concern variants "most clear".
Is better to write all explicitly.
All IMHO.

#### Re: Subquery with sorting

fraks wrote:

In this case iif is the most pertinent.

me  with tons of brackets already in   that I happen, guilty business I receive some fields from the server where all is already placed, instead of in  though would be ideologically more correct to garble the data on the client, without straining the server.

fraks wrote:

Is better to write all explicitly.

yes, and, normally, and I do: type as for  fields and a colon at parameter assignment in  .