Create a view to show average over a set

I have a table,

tbl_courses 
------------
int id //pk
int id_formation //fk
int credits
enum lang // en, fr, etc

The data in the table looks like this

id | id_formation | credits | lang
1 | 101 | 4 | en
2 | 101 | 6 | en
3 | 101 | 5 | fr
4 | 102 | 8 | en
5 | 102 | 2 | fr
6 | 103 | 3 | fr

I want to have a view that shows the per开发者_如何学编程centage of lang-credits for each id_formation, something like

id_formation | en_percent | fr_percent
101 | 66 | 33
102 | 80 | 20
103 |  0 | 100

I managed to make a query to do this, but mysql won't let me make a view out of it

SELECT
a.id_formation,
en_percent
//fr_percent
FROM tbl_courses a
JOIN (
  SELECT
  a.id_formation, 
  FORMAT(SUM(a.credits)*100/b.total,0) AS 'en_percent'  
  FROM tbl_courses a
  LEFT JOIN (
    SELECT id_formation, SUM(credits) as total 
    FROM tbl_courses
    GROUP BY id_formation) AS b ON b.id_formation = a.id_formation
  GROUP BY a.id_formation, a.lang HAVING a.lang='en'
) AS en ON en.id_formation=a.id_formation
//repeat join code and switch en to fr
GROUP BY id_formation

I repeat the code in the JOIN to get the fr_percent. Is there a way to write this to make it view friendly?

I create a small test case with three languages. Look at the last query. Does it give you what you need? If so, I can probably help you create the view you want.

create table tbl_courses as
   select 1 as id_formation, 'en' as lang, 25 as credits union all
   select 1 as id_formation, 'fr' as lang, 50 as credits union all
   select 1 as id_formation, 'sv' as lang, 25 as credits union all
   select 2 as id_formation, 'en' as lang, 80 as credits union all
   select 2 as id_formation, 'fr' as lang, 15 as credits union all
   select 2 as id_formation, 'sv' as lang,  5 as credits;

alter table tbl_courses add primary key(id_formation, lang);

select id_formation
      ,count(*)     as num_languages
      ,sum(credits) as total_credits
      ,sum(case when lang = 'en' then credits else 0 end)  as en_creds
      ,sum(case when lang = 'fr' then credits else 0  end) as fr_creds
  from tbl_courses
 group 
    by id_formation;

+--------------+---------------+---------------+----------+----------+
| id_formation | num_languages | total_credits | en_creds | fr_creds |
+--------------+---------------+---------------+----------+----------+
|            1 |             3 |           100 |       25 |       50 |
|            2 |             3 |           100 |       80 |       15 |
+--------------+---------------+---------------+----------+----------+
2 rows in set (0.00 sec)    

Thanks to Ronnis, the trick is sum(case when):

SELECT
  id_formation,
  FORMAT(sum(case when lang = 'en' then credits else 0 end)*100 / SUM(credits),0) as en_percent,
  FORMAT(sum(case when lang = 'fr' then credits else 0 end)*100 / SUM(credits),0) as fr_percent
FROM
  tbl_courses 
GROUP BY id_formation