MySQL Forums
Forum List  »  Newbie

mysql - count records after ORDER BY
Posted by: Tomasz gagola
Date: September 27, 2018 05:53AM

I have the code to count records but unable to add order by before it.

Two tables are joined and I added the code to count records. The issue is that I want to first ORDER BY SN and assign cnt after?

My code is:

Tables

create table rot (
code int(10) primary key,
PN varchar(10) not null,
SN varchar(10) not null,
LocID int(10) not null);

insert into rot values (1,'T1','T1SN1','1');
insert into rot values (2,'A1','A1SN1','2');
insert into rot values (3,'J1','J1SN1','3');
insert into rot values (4,'A2','A2SN1','1');
insert into rot values (5,'J2','J2SN1','2');
insert into rot values (6,'A3','A3SN1','3');
insert into rot values (7,'J3','J3SN1','4');
insert into rot values (8,'T1','T1SN2','5');
insert into rot values (9,'A1','A1SN2','1');
insert into rot values (10,'J2','J2SN2','3');
insert into rot values (11,'J2','J2SN3','4');
insert into rot values (12,'A1','A1SN3','3');
insert into rot values (13,'J2','J2SN4','5');

create table loc(
code1 int(10) primary key,
LocVar varchar(10) not null);

insert into loc values (1,'AAA');
insert into loc values (2,'BBB');
insert into loc values (3,'CCC');
insert into loc values (4,'DDD');
insert into loc values (5,'EEE');
Cnt code:

SELECT * FROM rot
JOIN loc ON rot.code = loc.code1

JOIN (
SELECT t1.code, count(*) cnt FROM (
SELECT distinct code
FROM rot ts1
JOIN loc tx1 ON ts1.code = tx1.code1

) t1
JOIN (
SELECT distinct code
FROM rot ts2
JOIN loc tx2 ON ts2.code = tx2.code1

) t2 on t1.code <= t2.code
group by t1.code

) tt ON rot.code = tt.code
Results:

+------+----+-------+-------+-------+--------+------+-----+
| code | PN | SN | LocID | code1 | LocVar | code | cnt |
+------+----+-------+-------+-------+--------+------+-----+
| 2 | A1 | A1SN1 | 2 | 2 | BBB | 2 | 4 |
| 4 | A2 | A2SN1 | 1 | 4 | DDD | 4 | 2 |
| 3 | J1 | J1SN1 | 3 | 3 | CCC | 3 | 3 |
| 5 | J2 | J2SN1 | 2 | 5 | EEE | 5 | 1 |
| 1 | T1 | T1SN1 | 1 | 1 | AAA | 1 | 5 |
+------+----+-------+-------+-------+--------+------+-----+
Desired results

+------+----+-------+-------+-------+--------+------+-----+
| code | PN | SN | LocID | code1 | LocVar | code | cnt |
+------+----+-------+-------+-------+--------+------+-----+
| 2 | A1 | A1SN1 | 2 | 2 | BBB | 2 | 1 |
| 4 | A2 | A2SN1 | 1 | 4 | DDD | 4 | 2 |
| 3 | J1 | J1SN1 | 3 | 3 | CCC | 3 | 3 |
| 5 | J2 | J2SN1 | 2 | 5 | EEE | 5 | 4 |
| 1 | T1 | T1SN1 | 1 | 1 | AAA | 1 | 5 |
+------+----+-------+-------+-------+--------+------+-----+
I just wonder where to place ORDER BY? In my code I am unable to assign variables and the code must start with SELECT.

Options: ReplyQuote


Subject
Written By
Posted
mysql - count records after ORDER BY
September 27, 2018 05:53AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.