SQL Statement Group By Number, but need 0 count for non existing numbers-CodePudding

I am trying to do a count on records of certain baseball cards that use a grading scale.

Right now I have it going through a loop of each card in a set using count function to count the total of each card with a certain grade, which causes 100's of querys and is extremely slow.

I tried using a GROUP BY statement, which does what I need, but doesnt account for grades that don't have any cards in that grade, where I would need it to show a 0 to output in my table correctly..

The Grading scale I need a count for each SAME card is: 1, 1.5, 2, 2.5, 3, 3.5, 4, 4.5, 5, 5.5, 6, 6.5, 7, 7.5, 8, 8.5, 9, 10

If count is 0, I need it to show a 0

Current Query's to go through grades on the above scale

SELECT count(id) FROM cards WHERE set='1986', brand='fleer', cardnum='57' and grade='1';
SELECT count(id) FROM cards WHERE set='1986', brand='fleer', cardnum='57' and grade='1.5';
SELECT count(id) FROM cards WHERE set='1986', brand='fleer', cardnum='57' and grade='2';
// and so on...

Here is the group by query that does what I need it to do, but doesnt create a row for cards with a 0 count of that grade.

SELECT count(id) as TOTAL, GRADE from cards where YEAR='1986' and BRAND='FLEER' and SPORT='BASKETBALL' and CARDNUM='57' GROUP BY GRADE order by `GRADE` 0;

CodePudding user response：

You can query the things that exists. You can use a LEFT JOIN with the desired grades:

SELECT count(c.id) as TOTAL, q.GRADE
from (
  select '1' as GRADE
  union 
  select '1.5'
  union 
  select '2'
  union 
  select '2.5'
  union 
  select '3'
  union 
  select '3.5'
  union 
  select '4'
  union 
  select '4.5'
  union 
  select '5'
  union 
  select '5.5'
  union 
  select '6'
  union 
  select '6.5'
  union 
  select '7'
  union 
  select '7.5'
  union
  select '8'
  union 
  select '8.5'
  union
  select '9'
  union 
  select '10'
 ) q 
 left join cards c on q.GRADE=c.GRADE and c.YEAR='1986' and c.BRAND='FLEER' and c.SPORT='BASKETBALL' and c.CARDNUM='57' 
GROUP BY q.GRADE 
order by q.GRADE   0;