How to use multiple count and group by condition in single SQL query -

below sql table.

    id   filename       code1    code2     1   002-03_001.tif  y179     y179     2   002-03_002.tif  y178     y178     3   002-03_003.tif  y177     y177     4   002-03_004.tif  y178     y179     5   002-03_005.tif  y177     y179     6   002-03_006.tif  y179     y178     7   002-03_007.tif  y178     y178     8   002-03_008.tif  y178     y177     9   002-03_009.tif  y177     y179     10  002-03_010.tif  y178     y177 

from above table want make count of code1 , code2 like,

  code1 count1 code2 count2   y177   3      y177    3   y178   2      y178    3   y179   5      y179    4 

you want sql fiddle

sample data

create table table1 ( id int,filename varchar(100),code1 varchar(10),code2 varchar(10) )  insert table1 values(    1,   '002-03_001.tif',  'y179',     'y179'),     (2   ,'002-03_002.tif',  'y178',     'y178')     ,(3   ,'002-03_003.tif',  'y177',     'y177')     ,(4   ,'002-03_004.tif',  'y178',     'y179')     ,(5   ,'002-03_005.tif',  'y177',     'y179')     ,(6   ,'002-03_006.tif',  'y179',     'y178')     ,(7   ,'002-03_007.tif',  'y178',     'y178')     ,(8   ,'002-03_008.tif',  'y178',     'y177')     ,(9   ,'002-03_009.tif',  'y177',     'y179')     ,(10  ,'002-03_010.tif',  'y178',     'y177') 

query

select * (     select code1,count(*) count1     table1     group code1 )   code1 inner join  (     select code2,count(*) count2     table1     group code2 ) code2 on code1.code1 = code2.code2