join - Wrong data on SQL query -

my requirement fetch details 2 tables report_api_usage & report_api_pages. need termname, count(termname) report_api_usage , count(ctype) both event , download report_api_pages. both table has common field termid. requirement termname, count(termname) related events , file-download.

there 1 termid 24 in report_api_pages ctype download. result should face-to-face learning 1 0 1

i have page face-to-face learning termid'24. have added event , document reference offace-to-face learning. once viewedface-to-face learningpage,count(termname)will store page hit value inreport_api_usagetable. once event , document pages viewed,report_api_usagewill store details withtermidreference. need create report of views. need getface-to-face learning` view count related event , document hits.

you have 2 rows termname='face-to-face learning' in table report_api_usage therefore returned 2 lines.

you should use:

select ap.termname, count(distinct ap.termname) tot, count(distinct ai.ctype) event, sum(case when ai.ctype = 'event' 1 else 0 end) page ... 

i don't understand want count.. if want count how many repoet_api_pages have ctype 'event' or 'download' use follows query:

select ap.termname, count(ap.termname) tot, -- if total count rows in table report_api_usage, if need unique count use distinct inside count function sum(case when ai.ctype = 'download' 1 else 0 end) page_download sum(case when ai.ctype = 'event' 1 else 0 end) page_event ...