CODEWITHSUNDEEP
mysqljoinselectsum
Max Thorley
Max Thorley

Reputation: 173

MySQL - Compare TWO tables and SUM them

I'm struggling on how to compare to TWO tables against eachother whilst totaling (SUM) them up.

For example i have the follow 2 tables:

TABLE 1

tablename: master
col1: code
col2: categories

DATA

 categories     code
 GP              a01
 GP              a02
 GP              a03
 DENTAL          d01
 DENTAL          d02
 DENTAL          d02
 DENTAL          d03
 DENTAL          d04
 OPTOM           o01

TABLE 2

tablename: submissions
col1: code

DATA

  code
  a01
  a02
  a03
  d04
  d01
  o01
  a03

I need to link both tables by code to see who has submitted in the submissions table and who hasnt then work out the overal percentage:

My ideal result:

categories   codes_total    received    outstanding   percentage received
category 1       200           40            160             20%
category 2       100           10            90              10%

I've thave no idea where to start really other than this??:

 SELECT categories, sum(total1), sum(total2), sum(total3) FROM (

  (SELECT categories, count(*) t1
  FROM master) as total1,

  (SELECT categories, count(*) t2
  FROM master
  LEFT JOIN submissions ON submissions.code=master.code) 
  )as total2,

  (SELECT categories, count(*) t3
  FROM master
  LEFT JOIN submissions ON submissions.code=master.code
  WHERE submissions.code IS NULL) 
  )as total3

) FOO
  GROUP BY categories

I am going in the right direction???

Upvotes: 0

Views: 58

Answers (1)

DanB
DanB

Reputation: 2124

select master.categories, count(*) codes_total,
        (select count(*)
                    FROM master master2
                            inner join submissions on master2.code = submissions.code
                    where master2.categories = master.categories) received,
        count(*) - (select count(*)
                    FROM master master2
                            inner join submissions on master2.code = submissions.code
                    where master2.categories = master.categories) `outstanding`,

        (select count(*)
                    FROM master master2
                            inner join submissions on master2.code = submissions.code
                    where master2.categories = master.categories) / count(*) * 100 as `percentage received`

from master

group by master.categories
order by master.categories;

Upvotes: 1

Related Questions