How do you sum the same products in an invoice?

I have this query:

我有这个问题:

declare @usuario as varchar(50), @fecha as datetime
set @usuario ='angel'
set @fecha ='2011/08/07'

select DetalleCompra .id_ali  as [Clave],Alimento .nomAli as [Nombre Alimento], 
    (select sum (cantidad) 
     from DetalleCompra 
     inner join Alimento on DetalleCompra .id_ali =Alimento .id_ali 
     where DetalleCompra .usuario =@usuario  and fecha =@fecha  and FolioCompra is null
    ) as Cantidad, 
    Alimento .precio as [Precio Unitario], ( sum (Cantidad) * precio ) as [Precio Total] 
from DetalleCompra , Alimento 
where DetalleCompra .id_ali =Alimento.id_ali 
    and usuario =@usuario  and  fecha =CONVERT(datetime, @fecha ) and FolioCompra  is null
group by DetalleCompra .id_ali ,Alimento .nomAli , Alimento .precio , DetalleCompra .Cantidad

where:

usuario=user
fecha=date
detallecompra=details buy
idali= id food
nomali=food's name
foliocompra=folio purchases
precio=price
cantidad=quantity
precio total=total price

I want to get a list, but if the products are the same, I want to sum them and get the total price of them.

我想得到一个清单,但如果产品是相同的,我想总结它们并得到它们的总价。

They have a username and a date, and they are null until the user "accepts" them.

它们具有用户名和日期,并且在用户“接受”它们之前它们为空。

I want to get the list where the idali appears twice or more they sum that row, and sum the total price too.

我想得到idali出现两次或更多的列表,他们总结那一行,并总和总价。

1 个解决方案

#1

I believe that you are looking for something like this.

我相信你正在寻找这样的东西。

The query groups the data by id (id_ali), name (nomAli) and price (precio) columns. It then sums up the quantity (cantidad) to create the Total Quantity column (I just wrote it in English). Also, formulates the Precio Total column using the quantity and price columns.

查询按id(id_ali),name(nomAli)和price(precio)列对数据进行分组。然后它总结了数量(cantidad)来创建总数量列(我刚用英文写的)。此外,使用数量和价格列制定Precio Total列。

If the column fecha contains only date values without any timestamps, you can do simple equal to comparison instead of using DATEDIFF.

如果列fecha仅包含没有任何时间戳的日期值,则可以使用简单等于比较而不是使用DATEDIFF。

DECLARE @usuario AS VARCHAR(50)
DECLARE @fecha AS DATETIME

SET @usuario ='angel'
SET @fecha ='2011/08/07'

SELECT      dc.id_ali   AS [Clave]
        ,   al.nomAli   AS [Nombre Alimento]
        ,   al.precio   AS [Precio Unitario]
        ,   SUM(cantidad)   AS [Total Quantity]
        ,   SUM(cantidad) * al. precio as [Precio Total]            
FROM        DetalleCompra   dc
INNER JOIN  Alimento        al
ON          dc.id_ali       = al.id_ali 
WHERE       usuario         = @usuario  
AND         FolioCompra     is null
AND         DATEDIFF(DAY, fecha, @fecha) = 0
GROUP BY    dc.id_ali 
        ,   al.nomAli 
        ,   al.precio

#1