CODEWITHSUNDEEP
mysqlsqljoingroup-by
user1499202
user1499202

Reputation: 13

SQL about join tables

There are 2 tables - table1 and table2 I would like to have the number of counts of the grade that val in the corresponding range

table1

╔════╦═════╗
║ id ║ val ║
╠════╬═════╣
║  1 ║ 80  ║
║  2 ║ 75  ║
║  3 ║ 72  ║
║  4 ║ 85  ║
║  5 ║ 70  ║
║  6 ║ 80  ║
║  7 ║ 76  ║
╚════╩═════╝

table2

╔═══════╦══════════╗
║ grade ║ boundary ║
╠═══════╬══════════╣
║  A    ║ 85       ║
║  B    ║ 80       ║
║  C    ║ 75       ║
║  D    ║ 70       ║
║  E    ║ 65       ║
║  F    ║  0       ║
╚═══════╩══════════╝

How can I have the result like:

╔═══════╦════════╗
║ grade ║ total  ║
╠═══════╬════════╣
║  A    ║ 1      ║
║  B    ║ 2      ║
║  C    ║ 2      ║
║  D    ║ 2      ║
║  E    ║ 0      ║
║  F    ║ 0      ║
╚═══════╩════════╝

I used to do it using PHP, but I want to do it using SQL only I not familiar with complicated join statement.

Upvotes: 1

Views: 88

Answers (3)

Booboo
Booboo

Reputation: 44043

select table2.grade, ifnull(total, 0) as total from table2 left join (
    select grade, count(*) as total from (
        select table2.grade from table1 join table2
        on table2.boundary = (select max(boundary) from table2 where boundary <= table1.val)
    ) sq
    group by grade
) sq on table2.grade = sq.grade
order by grade;

See DB Fiddle

Upvotes: 1

piyush
piyush

Reputation: 383

This can be done using the simple right join.

SELECT t2.grade,count(t1.val) FROM table1 t1
right join table2 t2
on t1.val=t2.boundary
GROUP by t2.grade,t1.val
order by t2.grade ASC

1- Below code will just join the table and select all columns.

SELECT * FROM table1 t1
right  join table2 t2
on t1.val=t2.boundary

enter image description here

2- Adding groupby will group the element on grade and count the repeated data.

SELECT t2.grade,count(t1.val) FROM table1 t1
right join table2 t2
on t1.val=t2.boundary
GROUP by t2.grade,t1.val
order by t2.grade ASC

Upvotes: 0

Mark Taylor
Mark Taylor

Reputation: 1188

yes this is very doable. Personally I'd change the table2 structure to include upper and lower limits.

Assuming you can't do that this solution simulates that with a CTE which allows direct bounding. I've also used a subquery to still show the grades with no population - if you don't care about rows with zero records you can ignore this bit.

This is SQL Server Syntax (http://sqlfiddle.com/#!18/f1e9b/1) but the principle will work should work fine with most RDBMS' :) Enjoy

--setup
create table scores (

  id integer,
  val integer
  );

create table boundaries (

  grade varchar(max),
  boundary integer
  );

INSERT INTO scores values (1,80);
INSERT INTO scores values (2,75);
INSERT INTO scores values (3,72);
INSERT INTO scores values (4,85);
INSERT INTO scores values (5,70);
INSERT INTO scores values (6,80);
INSERT INTO scores values (7,76);

INSERT INTO boundaries values ('A',85);
INSERT INTO boundaries values ('B',80);
INSERT INTO boundaries values ('C',75);
INSERT INTO boundaries values ('D',70);
INSERT INTO boundaries values ('E',65);
INSERT INTO boundaries values ('F',0);

--query
with rangesCTE as (
SELECT 
  grade
  , boundary as lowerBoundary
  , coalesce (LAG (boundary,1) OVER (ORDER BY boundary desc),100) AS higherBoundary
FROM boundaries)

select 
  boundaries.grade
  , coalesce (sq.total,0) as total
from boundaries 
left join (
  select 
    rangesCTE.grade as grade
    , count(*) as total
  from scores
  left join rangesCTE on scores.val >= rangesCTE.lowerBoundary and scores.val < rangesCTE.higherBoundary
  group by rangesCTE.grade
) sq on boundaries.grade = sq.grade

Upvotes: 0

Related Questions