MYSQL combining two counts that use same columns

Question

I have two queries that i need to combine into one query. The problem is they are using the same columns in where clause depending on what i need to fetch.

Query 1

SELECT c.fullname, COUNT( DISTINCT sst.id ) AS   'Liczba rozpoczetych szkolen'
FROM mdl_course c
INNER JOIN mdl_scorm s ON s.course = c.id
INNER JOIN mdl_scorm_scoes_track sst ON s.id = sst.scormid
INNER JOIN mdl_user u ON u.id = sst.userid
WHERE sst.element =  'x.start.time'  AND u.deleted =0
GROUP BY c.fullname ORDER BY `Liczba rozpoczetych szkolen` ASC

Query 2

SELECT c.fullname, COUNT(DISTINCT sst.userid ) AS  'Liczba_ukonczonych_szkolen'
FROM mdl_course c
INNER JOIN mdl_scorm s ON s.course=c.id
INNER JOIN mdl_scorm_scoes_track sst ON s.id = sst.scormid
INNER JOIN mdl_user u ON sst.userid=u.id
where `element`='cmi.core.score.raw' and `value` = 100 and u.deleted = 0
GROUP BY c.fullname ORDER BY `Liczba_ukonczonych_szkolen` DESC

They are depending on the same column named 'element'.

How i can display the result as

fullname Liczba rozpoczetych szkolen  Liczba_ukonczonych_szkolen 
   A1                  34                       4
   A2                   5                       3
   A3                   34                      33

I've came up with this one, whitch works. Thanks for advice @HoneyBadger

    SELECT t1.fullname, t1.Liczba_rozpoczetych_szkolen, t2.Liczba_ukonczonych_szkolen,
    round(((t2.Liczba_ukonczonych_szkolen /t1.Liczba_rozpoczetych_szkolen)*100),2) as procentowo
    FROM 
    (SELECT c.fullname, COUNT( DISTINCT sst.id ) AS   Liczba_rozpoczetych_szkolen
    FROM mdl_course c
    INNER JOIN mdl_scorm s ON s.course = c.id
    INNER JOIN mdl_scorm_scoes_track sst ON s.id = sst.scormid
    INNER JOIN mdl_user u ON u.id = sst.userid
    WHERE sst.element =  'x.start.time'  AND u.deleted =0
    GROUP BY c.fullname )  AS t1,
    (SELECT c.fullname, COUNT(DISTINCT sst.userid ) AS  Liczba_ukonczonych_szkolen
    FROM mdl_course c
    INNER JOIN mdl_scorm s ON s.course=c.id
    INNER JOIN mdl_scorm_scoes_track sst ON s.id = sst.scormid
    left join mdl_user u ON sst.userid=u.id
    where `element`='cmi.core.score.raw' and `value` = 100 and u.deleted = 0
    GROUP BY c.fullname) as t2
    WHERE t1.fullname = t2.fullname

Your LEFT JOIN is really INNER as restricted in WHERE with non-null predicate u.deleted = 0 Are you sure? — Serg
– Serg, Commented Jun 20, 2017 at 9:18
@Jotosha, that is not entirely what I had in mind. Please see my answer. — HoneyBadger
– HoneyBadger, Commented Jun 20, 2017 at 9:29

HoneyBadger · Accepted Answer · 2017-06-20 09:33:51Z

You can join them together like this:

SELECT      COALESCE(R.fullname, U.fullname) AS fullname
,           COALESCE(R.Liczba_rozpoczetych_szkolen, 0) AS Liczba_rozpoczetych_szkolen
,           COALESCE(R.Liczba_ukonczonych_szkolen, 0) AS Liczba_ukonczonych_szkolen
FROM        (
                SELECT      c.fullname
                ,           COUNT( DISTINCT sst.id ) AS   Liczba_rozpoczetych_szkolen
                FROM        mdl_course c
                INNER JOIN  mdl_scorm s 
                        ON  s.course = c.id
                INNER JOIN  mdl_scorm_scoes_track sst 
                        ON  s.id = sst.scormid
                INNER JOIN  mdl_user u 
                        ON  u.id = sst.userid
                WHERE       sst.element =  'x.start.time'  
                        AND u.deleted =0
                GROUP BY    c.fullname
            ) AS R
FULL JOIN   (
                SELECT      c.fullname
                ,           COUNT(DISTINCT sst.userid ) AS  Liczba_ukonczonych_szkolen
                FROM        mdl_course c
                INNER JOIN  mdl_scorm s 
                        ON  s.course=c.id
                INNER JOIN  mdl_scorm_scoes_track sst 
                        ON  s.id = sst.scormid
                INNER JOIN  mdl_user u 
                        ON  sst.userid=u.id
                where       `element`='cmi.core.score.raw' 
                        AND `value` = 100 
                        AND u.deleted = 0
                GROUP BY    c.fullname
            ) AS U
        ON  R.fullname = U.fullname

Serg · Accepted Answer · 2017-06-20 09:26:25Z

1

Try the query with the ORed predicates and extra filtering with CASE in the SELECT list. Kind of

SELECT c.fullname, COUNT(DISTINCT CASE WHEN sst.element =  'x.start.time' THEN  sst.id END) AS   'Liczba rozpoczetych szkolen'
, COUNT(DISTINCT CASE WHEN sst.element =  'cmi.core.score.raw' and `value` = 100 THEN  sst.userid END) AS  'Liczba_ukonczonych_szkolen'
FROM mdl_course c
INNER JOIN mdl_scorm s ON s.course = c.id
INNER JOIN mdl_scorm_scoes_track sst ON s.id = sst.scormid
INNER JOIN mdl_user u ON u.id = sst.userid
WHERE sst.element = IN( 'x.start.time', 'cmi.core.score.raw')  AND u.deleted =0
GROUP BY c.fullname ORDER BY `Liczba rozpoczetych szkolen` ASC

answered Jun 20, 2017 at 9:26

Serg

22.8k5 gold badges23 silver badges50 bronze badges

Collectives™ on Stack Overflow

MYSQL combining two counts that use same columns

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related