Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

table 1 : categories

--------------------------------------------------------------
cat_id |  cat_title
--------------------------------------------------------------
1      |  sports 
2      |  news
3      |  programming
--------------------------------------------------------------

table 2 : sites

--------------------------------------------------------------
site_id |  site_cat_id  | site_lang  |  site_title
--------------------------------------------------------------
1       |  3            | EN         |   any title
2       |  1            | FR         |   any title
3       |  1            | AR         |   any title
4       |  2            | EN         |   any title
5       |  1            | AR         |   any title
6       |  3            | FR         |   any title
7       |  1            | EN         |   any title
--------------------------------------------------------------

Requested Result

--------------------------------------------------------------
cat_id |  count_EN_sites  | count_FR_sites | count_AR_sites
--------------------------------------------------------------
1      |  1               | 1              | 2
2      |  1               | 0              | 0
3      |  1               | 1              | 0
--------------------------------------------------------------

My Trial :

SELECT `dir_cats`.`cat_id`, 
        SUM(CASE WHEN dir_sites.site_lang = "ar" THEN 1 ELSE 0 END) AS ar_count, 
        SUM(CASE WHEN dir_sites.site_lang = "en" THEN 1 ELSE 0 END) AS en_count, 
        SUM(CASE WHEN dir_sites.site_lang = "fr" THEN 1 ELSE 0 END) AS fr_count
FROM (`dir_cats`)
LEFT JOIN `dir_sites` ON `site_cat_id` = `cat_id`
WHERE `dir_sites`.`site_private` =  0
GROUP BY `dir_sites`.`site_cat_id`

The Problem :

The result has duplicated count_EN , FR , AR sites VALUES appearing like this :

--------------------------------------------------------------
cat_id |  count_EN_sites  | count_FR_sites | count_AR_sites
--------------------------------------------------------------
1      |  20              | 20             | 20
2      |  1               | 0              | 0
3      |  40              | 40             | 40
--------------------------------------------------------------

20 20 20

40 40 40

are not actual results but they are duplicates

share|improve this question
    
Is this the exact query you run and the tables have only these (3 and 7) rows only? Seems weird. –  ypercube Nov 6 '13 at 13:17
    
This is the same query But not the same tables content , this is just sample –  Khashabawy Nov 6 '13 at 13:41
    
Well, you have site_private = 0 in the code but not in the tables. The query has AS ar_count but the result has the column named count_AR_sites. –  ypercube Nov 6 '13 at 13:43
    
Can you run SELECT COUNT(1) FROM dir_sites WHERE site_private = 0 AND site_cat_id = 3 and post the result? –  Sebastian Meine Nov 6 '13 at 13:50
    
What you show should not happen unless there is a bug. Most likely you have joined more tables in the query but you omitted that part of the code. –  ypercube Nov 9 '13 at 21:40

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.