How to get number of rows from another table?

Question

I have a table (table1) with a list of students from the highest to the lowest grade. I want to divide them in 3 groups. But how many students in each group? First I count how many student I have, then I look in table1 for the row where column NumberStudent is equal to the total of students if I found. I take the number of group1 which means the number of students in group1. The student should not be repeated in other groups.

table2
contains how many students in each groups according to their number

-------------------------------------------
 NumberStudent| group1 | group2 | group3 |
 -----------------------------------------
       1      |    1   |   0    |   0    |  
       2      |    2   |   0    |   0    |
       3      |    2   |   1    |   0    |
       4      |    2   |   2    |   0    |
       5      |    2   |   2    |   1    |
 -----------------------------------------

For 5 students, group1 = 2 students, group2 = 2 students and group3 = 1 student
For 3 students, group1 = 2 students, group2 = 1 student and group3 = 0 students

table1

+----+----------+------------+
| id | name     | Marks      | 
+----+----------+------------+
|  1 | Bertrand | 17         | 
|  2 | Charles  | 10         |
|  3 | Alex     | 12         | 
|  4 | David    | 11         | 
|  5 | Eric     | 20         | 
|  6 | François | 20         |
|  7 | Gaston   | 18         | 
|  8 | Henri    | 20         | 
+----+----------+------------+

I want to count how many students are in Table1

select count(Id) as Total from Table1

For example, if I have 5 students, I divide them into 3 groups according to the number of Table2.

Table2

NumberStudent| group1 | group2 | group3 |
-----------------------------------------
      5      |    2   |   2    |   1    |

I have 2 students in Group1, 2 students in Group2, 1 student in Group3

select name,Marks from Table1 
where Marks >=10
order by Marks row //from table2 (how can i obtain the number row =2 as parameter )

Group2 has 2 students which are not found in group1

select name,Marks from Table1 
where Marks >=15 and id<> id // the student in groupe2 not mention in group1
order by Marks  rowrow //from table1 (group2 for 5 student is 2 so row =2)

Group3 has 1 student which is not found in group1

select name,Marks from Table1 
where Marks >17 and id<> id // the student in groupe3 not mention in group1 and group2
order by Marks row row //from table1 (group3 for 5 student is 1 so row =1)

The result should be

  1  Henri     20    group1      
  2  Eric      20    group1        
  3  François  20    group2            
  4  Gaston    18    group2           
  5  Bertrand  17    group3

Looks like you need to learn how to `join` in your queries, and/or use subqueries. However it's a bit unclear what you actually want from us. — Jerry Dodge, Dec 10 '18 at 16:32
what i want is how to get the number of row of each group from another Table(in my question Table1) — Sahat, Dec 10 '18 at 18:55
Why does the row number matter? The row # can differ depending on many things. Surely you need to reference the ID instead. — Jerry Dodge, Dec 10 '18 at 18:58
what i want is how to get the number of row of each group from another Table(in my question Table1) if i have 2 students i look for the number of students in column NumberStudent and take the number of each group(group1,group2 and group3) i put it in row as variable select name,Marks from Table1 where Marks >=15 and id<> id // the student in groupe2 not mention in group1 order by Marks row //from table1 (group2 for 5 student is 2 so row =2) — Sahat, Dec 10 '18 at 19:08
Looks like you are doing your homework in school.... That is risky: teachers do not always emphasize what is suggested mainstream or best industry practice, but may emphasize what they told in their lectures. So, beware. Our advices here might be well-known to teacher and used by him to check you did not worked through his lectures. — Arioch 'The, Dec 11 '18 at 08:44
First of all, names `Table1` and `Table2` mean nothing. Please, give those table meaningful both names and descriptions what do they contain. For example I may try to guess that second table is list of all students. But I am NOT sure, I only guesses. I may guess, that the first table's `NumberStudent` is equal to `ID` form the second table. But I am not sure. What about the *meaning* of the first table I totally lost. Describe in normal human language what data your tables should carry. P.S. the said description and names please add to the question itself (_edit_ it), not to the comments. — Arioch 'The, Dec 11 '18 at 08:47
i'm not a student!! table1 contains how many students in each groups according to their number which i want put is as parameter in the row of the requete — Sahat, Dec 11 '18 at 12:23
Basically u seem to say (but you do not, so I am again just GUESSING) that Your `NumberStudent == group1+group2+group3` - usually there is no reason to keep this aggregate data in the database. Also what would you do, if next day there would be `group 2` deleted (in real world)? What would you do if another day `group 4` would be created (in real world)? those `group x` should be not columns but rows in SQL. You can always easily insert rows and delete rows, while inserting and deleting columns is hard. So data like `group #10` is almost always kept as rows, except for very special cases — Arioch 'The, Dec 11 '18 at 12:38
And additionally you need to make table which students are members of each groups, assuming every student may belong to many groups at once or to no any group at all. So you have to 1) determine and spell out rules how students may or may not be assigned to groups 2) design external tables or columns that would express those assignments in the database [ like: student 11 belongs to group 3 but not to group 4 ] 3) build summaries as a query over that primary data, and `table 2` is not to be needed in the database at all, maybe rarely as cache of huge data — Arioch 'The, Dec 11 '18 at 12:43
i have a Table1 contains list of students are arranged from the highest to the lowest grade what i want is divided them to 3 groups .but how many students in each group? first i count how many student i have and then i look for inthe table1 in the column NumberStudent which number= total of students if i found it i take the number of group1 which mean the number of students in group1 Provided that the student is not repeated in the groups — Sahat, Dec 11 '18 at 17:42
`Table1` as shown above can not contain list of students - there is no data about any specific student at all, no StudentName column, not StudentGender or StudentClass, nothing like that. // Table2 can be and probably is list of students. That again about naming variables (and tables) in meaningful human-readable way. // What is the group thing? Is it some outside thing like classes, courses, teachers, which are decided in real world and database only registers it? //// see, overall u have to talk out "business domain" - real world situation and problems,then make DB structure,then write code — Arioch 'The, Dec 12 '18 at 08:33
Before you lay out the real world problem and constraints and use cases neither you nor anyone here can come to designing database structures to register and keep the real world data, and before the structure is decided upon no one can work on code accessing that not yet known structure. — Arioch 'The, Dec 12 '18 at 08:36
hello Arioch 'The the data about Students are in the Table2 their Names and their Grades etc...Table1 is a table have the number of each group (how many student are there in group1,how many student are there in group2 and how many student are there in group3) The question is clear: I want to divide the students into three groups. To obtain the number of each group, we use Table 1. The first column represents the number of students, which we will compare by counting the number of students in the table2. — Sahat, Dec 12 '18 at 20:59

score 1 · Accepted Answer · answered Dec 13 '18 at 16:39

1

If I understand correctly, you want to divide the students into groups, and the students with the highest Marks should go in the first group etc. and students with lowest Marks in last group? You seem to use a Table2 to essentially lookup the group sizes, but why not just calculate them?

I would use a subselect to rank all students by Marks and then divide that ranknumber over the disired group size to generate the group numbers.

I'm not entirely sure about the correct Firebird syntax (don't have firebird 3.0 here), but something like this:

declare @MyGroupSize double;
set @MyGroupSize = 5.0;

select
    floor(a.RANKNO / @MyGroupSize) as GROUPNO,
    (a.RANKNO / @MyGroupSize) as TEST123,
    a.RANKNO,
    a.id,
    a.name,
    a.Marks
from
    (
        SELECT
            row_number() over(order by Marks DESC)-1 as RANKNO,
            id,
            name,
            Marks
        FROM
            Students
    ) a

answered Dec 13 '18 at 16:39

BdR

2,770
2
17
36

thank you for your reply yes i want to divide the students into groups, and the students with the highest Marks should go in the first group etc and the student should not be repeated in other groups. First I count how many student I have in Table1.select count(id) as total from table1, then I look in table2 for the row where column NumberStudent is equal to the total to get the number of each group. – Sahat Dec 14 '18 at 14:37
Okay, but did you try my SQL example? It's a different approach, but does that give the result you want? Btw you can change the `5.0` to `2.0` or any other group size. – BdR Dec 15 '18 at 23:23
but your my sql example dvided them more than tree groups i want only 3 groups .It does not matter if all students are in groups. It is important to include the student how has the largest grades.and the student should not be repeated in other groups – Sahat Dec 17 '18 at 18:52

How to get number of rows from another table?

1 Answers1