Good afternoon I have three tables in sql I want to know which is the best-selling product EYE I do not need to have the stock as data
create table Producto(
id_prod int primary key,
nombre varchar(15)
Precio decimal(2,2)
)
create table Venta(
idventa int primary key,
fecha date
)
create table Detalle(
id_detalle int primary key,
idventa int,
id_prod int
)
Did you forget the column 'amount' in the table 'Detail' ?. Group by the product identifier in the 'Detail' table, order - descendingly - by the account of elements that each group contains and take the rows that correspond with the highest account value. If you have the column 'quantity', instead of using the function COUNT () it takes the function SUM (quantity).
SELECT TOP(1) WITH TIES p.nombre, COUNT(*) AS [Cantidad]
FROM
Detalle d
INNER JOIN Producto p ON d.id_prod = p.id_prod
GROUP BY d.id_prod, p.nombre
ORDER BY Cantidad DESC;
GO