Mysql count with group BY join and case-CodePudding

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.

CodePudding user response：

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;

CodePudding user response：

I will give one example Consider tests has structure like below,

id	test_name
1	test1
2	test2
3	test3

Now packages table structure is like below,

id	package_name
1	package1
2	package2

Now i will assign test to packages like below,

id	test_id	package_id
1	1	1
2	2	1
3	3	1
4	3	1

in above table

last entry is duplicated because test_id 3 assigned to package_id 1 so that's why i want to add group by or distinct to remove duplicated and count it as 1 instead of 2. example: if i want to fetch package_id 1 total mapped tests count then i should get count = 3 instead of 4