Mysql count with group BY join and case

user_6000812 0
Viewed 47

I have below tables,

packages

id, package_name

tests

id, test_name, test_type

test_package_mapping

id, test_id, package_id

Now i want to fetch count of mapped tests for a particular package_id with test types

Example:

i have two tests types

1. MCQ
2. Answer Upload

SO i want to fetch the count of mapped tests like below,

mcqtestcount : 30
answeruploadtestcount: 20

in test_package_mapping sometimes by mistake test_id will be duplicated so i want to add group by as well,

so far i tried query like below,

$packageid = 131;

$sql = "SELECT COUNT(DISTINCT(case when u.test_type ='MCQ' then 1 end)) AS test_count,
                COUNT(DISTINCT(case when u.test_type ='Answer Upload' then 1 end)) AS answeruploadtestcount
                FROM tests AS u
                INNER JOIN (
                     SELECT test_id
                     FROM test_package_maping
                     WHERE test_package_maping.package_id = $packageid
                     GROUP BY test_package_maping.test_id
                ) AS a ON a.test_id = u.test_id where u.test_type = 'Mains'";
$testscount = DB::select($sql);

Above query is not working and when i try different method to get count with group by it is showing 1 even though it has more records. Any solution? Thank you.

laravel mysql php
1 Answers

You can get the desired result by grouping by test_type in the joined table of both tests and test_package_mapping.

Please try to use the following SQL.

And you need to add an index on test_type column in tests table.

SELECT
  t.test_type,
  COUNT (t.id) AS testCount
FROM
  tests AS t
  INNER JOIN test_package_mapping m
    ON t.id = m.test_id
WHERE m.package_id = $ packageid
GROUP BY t.test_type;
user_83091130
Related