mysql multi group query with count

I have this query

SELECT 
   products_list.id_cat_unique,
   products_categories_list.*,
   COUNT(products_list.id_cat_unique) as counter
FROM products_categories_ids
LEFT JOIN products_list 
   ON products_categories_ids.id_cat_unique = products_list.id_cat_unique
LEFT JOIN products_categories_list 
   ON products_categories_list.id_cat_unique = products_categories_ids.id_cat_unique 
GROUP BY products_categories_list.name_cat
ORDER BY products_categories_list.name_cat ASC

This is complicated to explain without having the tables on your screen but I'll try

what I want is count all the rows from products_list that can be associated with max two rows per id_cat_unique (contained in products_categories_list) that are English or Italian. This two items each could have infinite id_products in products_list of course. I want the result to be grouped by products_categories_list.name_cat and by the language. What I get with this query is categories grouped by name/unique id which is correct, the prob开发者_如何学Pythonlem is that the counter value for both English and Italian turns out as the sum of all the products associated with one id_cat_unique regardless of the language. So if I have 12 rows from one category in English and 3 for the same category in Spanish I get 15 as counter for both the languages.

EDIT ADDING TABLES STRUCTURES

products_list

+-------------------+--------------+------+-----+---------+----------------+
| Field             | Type         | Null | Key | Default | Extra          |
+-------------------+--------------+------+-----+---------+----------------+
| id_product        | int(11)      | NO   | PRI | NULL    | auto_increment |
| id_product_unique | int(5)       | NO   |     | NULL    |                |
| lang              | varchar(2)   | NO   |     | NULL    |                |
| name_product      | varchar(200) | NO   |     | NULL    |                |
| desc_product      | text         | NO   |     | NULL    |                |
| id_cat_unique     | int(2)       | NO   |     | NULL    |                |
| status            | int(1)       | NO   |     | NULL    |                |
+-------------------+--------------+------+-----+---------+----------------+
12 rows in set (0.00 sec)

products_categories_ids

+---------------+--------+------+-----+---------+----------------+
| Field         | Type   | Null | Key | Default | Extra          |
+---------------+--------+------+-----+---------+----------------+
| id_cat_unique | int(5) | NO   | PRI | NULL    | auto_increment |
+---------------+--------+------+-----+---------+----------------+
1 row in set (0.00 sec)

products_categories_list

+---------------+--------------+------+-----+---------+----------------+
| Field         | Type         | Null | Key | Default | Extra          |
+---------------+--------------+------+-----+---------+----------------+
| id_cat        | int(5)       | NO   | PRI | NULL    | auto_increment |
| id_cat_unique | int(2)       | NO   |     | NULL    |                |
| lang          | varchar(2)   | NO   |     | NULL    |                |
| name_cat      | varchar(500) | NO   |     | NULL    |                |
| date_created  | int(11)      | NO   |     | NULL    |                |
| date_modified | int(11)      | NO   |     | NULL    |                |
+---------------+--------------+------+-----+---------+----------------+
6 rows in set (0.01 sec)

result from query

| id_cat_unique | id_cat | id_cat_unique | lang | name_cat                       | date_created | date_modified | counter |
+---------------+--------+---------------+------+--------------------------------+--------------+---------------+---------+
|             1 |     18 |             1 | it   | Carne di suino                 |   1308267538 |    1308267538 |       6 |
|            14 |     21 |            14 | it   | Guanciali                      |   1308777322 |    1308777322 |       2 |
|             3 |     20 |             3 | it   | Pollo a pezzi                  |   1308267892 |    1308267892 |       2 |
|             1 |     22 |             1 | en   | Pork meat                      |   1308267538 |    1312383232 |       6 |
|            14 |     23 |            14 | en   | Sheeps                         |   1308777322 |    1312383220 |       2 |
|             2 |     19 |             2 | it   | That's a "test" |   1308267538 |    1308267538 |       7 |
+---------------+--------+---------------+------+--------------------------------+--------------+---------------+---------+
6 rows in set (0.00 sec)

"Pork meat" and "Carne di Suino" have respectively 1 and 5 products (from the same category but different language, i wanna group the counting by language too) instead it shows 6 for both

NEW question

I'd like to use this query for many purposes without using different things, so accordingly if I want to retrieve the categories with ONE language I'd add one where clause

select pcl.*,
   (select count(*) from products_list pl 
       where pcl.id_cat_unique = pl.id_cat_unique 
         and pcl.lang = pl.lang) as counter
from products_categories_list pcl
where products_list.lang = 'en' <- added
ORDER BY pcl.name_cat ASC

I get Unknown column 'products_list.lang' in 'where clause'. Why?

I THINK this will work, if not, it's going to be close, let me know what you get, and I can modify it. I've modified the query around. Would this work?

select pcl.*,
   (select count(*) from products_list pl 
       where pcl.id_cat_unique = pl.id_cat_unique 
         and pcl.lang = pl.lang) as counter
from products_categories_list pcl
ORDER BY pcl.name_cat ASC

The PCL and PL are just aliases for the tables, so I didn't have to always write out products_categories_list and products_list. You specify the alias in the from part of the statement, so from products_list pl allows you to refer to products_list as pl in the rest of the query.

The idea behind this is that you have a unique row for each products_categories_list and you want a count of a secondary table. This could have been done by group by or by a sub query. I put the correlated subquery in to count the number of rows in products_list that matched the products_categories_list and language, since you are getting all the columns out of the products_categories_list. The correlated subquery allows you to get a single value (count(*)) from a nested query.

Probably this: add DISTINCT

COUNT(DISTINCT products_list.id_cat_unique) as counter

This will ignore duplicate, so the 12+3 via the JOIN becomes 1+1