Muhammad Asif Raza Muhammad Asif Raza - 6 months ago 13
SQL Question

Mysql multiply number of same records

Here is a simple query

$fetch_comb = mysql_query("SELECT code_id , atb_name_id FROM products_attributes pa WHERE pa.products_id = $products_id ");


I have an output like this

-----------------------
| code_id |atb_name_id|
-----------------------
| 1 | 31 |
| 2 | 31 |
| 3 | 31 |
| 4 | 31 |
| 5 | 34 |
| 6 | 34 |
| 7 | 37 |
| 8 | 37 |
| 9 | 37 |

column `atb_name_id` has
"31" = 4 records,
"34" = 2 records and
"37" = 3 records


I want to multiply these records in the query i.e 4 * 2 * 3 = 24

Answer

There is no PRODUCT function in SQL, but there is a workaround. You can try this:

SELECT CEILING(EXP(SUM(LOG(cnt))))
FROM (
    SELECT atb_name_id, COUNT(*) AS cnt
    FROM products_attributes pa 
    WHERE pa.products_id = $products_id
    GROUP BY atb_name_id
) t

ONLINE DEMO

Comments