Odtnhj

I have a table that shows all the products, the price_fact field, calculates the average when invoiced and updates the price_fact field when it makes an insert, and the price_alb field is the same but with a delivery note.

So when invoice (price_fact) has no delivery note (price_alb) and when it is delivery note has no invoice.

+---------------+------------+-------+-------------+-----------+
| name | id_product | price | price_fact| price_alb|
+---------------+------------+-------+-------------+-----------+
| phone | 1 | 300 | NULL | 275| 
| mouse | 2 | 100 | 100 | NULL| 
| phone | 1 | 250 | NULL | 275|
| mouse | 2 | 100 | NULL | 100| 
| phone | 1 | 300 | 300 | NULL|
+---------------+------------+------+--------------+-----------+

And what I want is to calculate all the products in a single row, the number of products in total and the final result shows the column price_fact and price_alb of that product, of those who have had records that are not null.

+---------------+------------+-------+-------------+-----------+------+
| name | id_product | price | price_fact| price_alb| units|
+---------------+------------+-------+-------------+-----------+------+
| phone | 1 | 300 | 300 | 275| 3|
| mouse | 2 | 100 | 100 | 100| 2| 
+---------------+------------+------+--------------+-----------+------+

Solved (I have put MAX(price_fact), MAX(price_alb) in select).

You can try to use aggregate function.

SELECT name,
 id_product,
 MAX(price) price,
 MAX(price_fact) price_fact,
 MAX(price_alb) price_alb,
 COUNT(*) units
FROM T
GROUP BY name,id_product