Mixing
JOIN WHERE and add calculated column
JOIN WHERE and add calculated column
Looking for the help. I try to run a code in this order JOIN, WHERE and and GRP number (on the results of my query).
I have two tables test and text:
create table test (
code varchar(10) primary key,
num int(10) not null,
name varchar(10) not null,
surname varchar(10) not null);
insert into test values (1,09,'Tom', 'Smith');
insert into test values (2,09,'Adam','Blake');
insert into test values (3,15,'John','Smith');
insert into test values (4,15,'Adam','XYZ');
insert into test values (5,43,'John','Abc');
insert into test values (6,99,'Adam','Abc');
insert into test values (7,99,'John','Abc');
insert into test values (8,15,'Adam','XYZ');
insert into test values (9,43,'John','Abc');
insert into test values (10,99,'Adam','Abc');
insert into test values (11,99,'John','Abc');
insert into test values (12,1,'A','A');
insert into test values (13,1,'J','A');
and table text:
create table text (
code varchar(10) primary key,
codeFK varchar(10) not null,
state varchar(10));
insert into text values (1,2,'O');
insert into text values (2,2,'O');
insert into text values (3,2,'O');
insert into text values (4,2,'C');
insert into text values (5,3,'O');
insert into text values (6,3,'O');
insert into text values (7,3,'O');
insert into text values (8,2,'O');
insert into text values (9,2,'C');
insert into text values (10,2,'O');
insert into text values (11,2,'C');
insert into text values (12,1,'C');
insert into text values (13,12,'C');
insert into text values (14,13,'C');
I join them and try to add coulm group as per below code:
JOIN - it works
SELECT num FROM test
LEFT JOIN text ON test.code = text.codeFK
WHERE state = 'O'
ORDER BY test.num DESC;
GRP calculation on its on
select t.*,
(select count(distinct t2.num)
from test t2
where t2.num <= t.num
) as grp
from test t ORDER BY GRP;
But my question is how to get GRP column with above join query? So, the grp column will be calculated only on the results based on WHERE statement and not the whole table test.
I tried below but it gives me an error:
SELECT num FROM test
LEFT JOIN text ON test.code = text.codeFK
WHERE state = 'O'
AND test.num IN (select t.num,
(select count(distinct t2.num)
from test t2
where t2.num <= t.num
) as grp from test t)
ORDER BY test.num DESC;
Desired results:
2 Answers
2
Try this:
select test.num, tt.cnt from test
join text on test.code = text.codefk
join (
select t1.num, count(*) cnt from (
select distinct num
from test ts1
join text tx1 on ts1.code = tx1.codefk
where tx1.state = 'O'
) t1 join (
select distinct num
from test ts2
join text tx2 on ts2.code = tx2.codefk
where tx2.state = 'O'
) t2 on t1.num <= t2.num
group by t1.num
) tt on test.num = tt.num
where text.state = 'O'
Another way, more efficient:
select @lagNum := 0, @cnt := 0;
select num, cnt from (
select case when num <> @lagNum then @cnt := @cnt + 1 end, @cnt cnt, @lagNum := num, num
from (
select (select num from test where code = t.codefk) num
from text t
where state = 'O'
) a order by num
) a
WOW! Thanks a lot.
– Kalenji
Jun 29 at 12:53
@Kalenji Try updated answer :)
– Michał Turczyn
Jun 29 at 13:10
@ Michal Turczyn. Dzieki jeszcze raz :)
– Kalenji
Jun 29 at 13:46
EDITED: This should point you in the correct direction. Instead of that second query in the AND it should be what youre selecting
AND
heres my fiddle: http://sqlfiddle.com/#!9/2fe48b/36
select t.num,
(select count(distinct t2.num) - 1
from test t2
where t2.num <= t.num
) as grp
from test t
LEFT JOIN text ON t.code = text.codeFK
WHERE text.state = 'O'
ORDER BY t.num DESC;
Thanks however it returns incorrect results. I bet it cannot give the correct results because SQL parsers read the WHERE clause before they read the SELECT expressions. I try having but it does not work either.
select t.num, t.name, (select count(distinct t2.num) from test t2 where t2.num <= t.num ) as grp from test t LEFT JOIN text ON t.code = text.codeFK GROUP BY t.num, t.name,grp,text.state HAVING text.state = 'O' – Kalenji
Jun 29 at 12:06
select t.num, t.name, (select count(distinct t2.num) from test t2 where t2.num <= t.num ) as grp from test t LEFT JOIN text ON t.code = text.codeFK GROUP BY t.num, t.name,grp,text.state HAVING text.state = 'O'
@Kalenji I updated it, I think I see what youre going for now with that grp column
– Static Void
Jun 29 at 12:11
By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.
the image of the desired results is not causing any harm. We can plainly see what the OP is trying to achieve from that.
– Yvette Colomb♦
Jun 29 at 12:03