Help in the sql query (sum)

Question

Help in the sql query (sum)

Navigation

#1 by (3 votes)
#2 by (1 votes)

2

I'm doing a Query where I add the amount of products that I have after multiplying it ..

This is the Query.

declare @Sucursalid int
set @Sucursalid = 1
    SELECT  i.sucursalid as SucursalID,( COUNT(p.id) * p.pesomililitros) AS Cantidad
         FROM inventario AS i
         INNER JOIN productos AS p ON i.productoid = p.id
         WHERE i.sucursalid = @Sucursalid AND i.consumido = 0 AND ( i.copeo IS NULL OR i.copeo IS NOT NULL ) AND i.copeo = 1
         GROUP BY I.sucursalid, p.pesomililitros

THIS IS THE RESULT

When it should be like this ..

SucursalID    Cantidad
1             2950

I hope you understand. Thanks.

Structure of the product table.

In itself, the value of pesomililitros can be different. 750ml 700ml 695ml

tells me this error. if I add sum(( COUNT(p.id) * p.pesomililitros)) AS Cantidad In the selection of sql ...

   cannot perform an aggregate function on an expression containing an aggregate or a subquery

sql c# sql-server

asked by JuanL 29.08.2018 в 18:12

source

2 answers

1

If what you want is that you only add the amount per branch you just have to indicate it in the group by by I.sucursalid and that will give you that result, more or less your code would be like that

declare @Sucursalid int
set @Sucursalid = 1

SELECT  i.sucursalid as SucursalID,
SUM(COUNT(p.id) * p.pesomililitros) AS Cantidad
FROM inventario AS i
INNER JOIN productos AS p ON i.productoid = p.id
WHERE i.sucursalid = @Sucursalid AND i.consumido = 0 AND ( i.copeo IS NULL OR i.copeo IS NOT NULL ) AND i.copeo = 1
GROUP BY I.sucursalid

answered by 29.08.2018 в 18:18

Where are the .sql files stored in windows? "Can not POST" in node.js and postman

score 3 · Accepted Answer

You could try trying the line in the following way.

declare @Sucursalid int
set @Sucursalid = 1

SELECT  i.sucursalid as SucursalID,
(COUNT(p.id) * SUM(p.pesomililitros)) AS Cantidad
FROM inventario AS i
INNER JOIN productos AS p ON i.productoid = p.id
WHERE i.sucursalid = @Sucursalid AND i.consumido = 0 AND ( i.copeo IS NULL OR i.copeo IS NOT NULL ) AND i.copeo = 1
GROUP BY I.sucursalid

What is done in the query is to count the number of records at the same time that add the milliliters and then perform the multiplication, this is how aggregate functions are no longer requested in the groupby and you can get the solution to your need.

Greetings, I hope it works for you.