Problem in a QUERY with COUNT

CREATE PROCEDURE [dbo].[GD_SP_HARDWARE_MONITOR_COUNT]

-- Add the parameters for the stored procedure here

@.Direccao nvarchar(10)

AS

DECLARE @.NrMon int

BEGIN

SELECT dbo.Monitor.MON_Monitor AS Item, COUNT(*) AS Unidades, dbo.Monitor.MON_CustoUnitario AS Total

FROM dbo.HARDWARE INNER JOIN

dbo.ADServico_User ON dbo.HARDWARE.UserID = dbo.ADServico_User.UserID INNER JOIN

dbo.SERVICO ON dbo.ADServico_User.GrupoServico = dbo.SERVICO.S_GrupoServico INNER JOIN

dbo.Monitor ON dbo.HARDWARE.MONITOR_ID = dbo.Monitor.MONITOR_ID

WHERE (dbo.HARDWARE.MONITOR_ID <> 5)

GROUP BY dbo.Monitor.MON_Monitor, dbo.Monitor.MON_CustoUnitario

END

DEAR FRIENDS,

HOW CAN I MULTIPLICATE THE VALUE FROM UNIDADES AND TOTAL?

Current output:

Unidades

/Total

TFT 17

417

/24,35

TFT 15

3254

/22,08

GOAL:

Unidades

/Total

TFT 17

417

/24,35

/10153,95

TFT 15

3254

/22,08

/71848,32

THANKS

Wrap your query in an outer query:

SELECT Item, Unidades, Total, Unidades*Total AS SomethingNew
FROM
(
Here comes your existing query
) SubQUery

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

THANKS!!!!!!

FANTASTIC!!!!

|||

Another question:
I made a UNION with 2 querys :

ALTER PROCEDURE [dbo].[GD_SP_FACTURA]

-- Add the parameters for the stored procedure here

@.Direccao nvarchar(10)

AS

BEGIN

SELECT Item, Unidades, CustoUnitario, Unidades*CustoUnitario AS Total

FROM

(

SELECT dbo.ModeloPC_Tipo.MOD_Nome AS Item, COUNT(*) AS Unidades, dbo.ModeloPC_Tipo.MOD_CustoUnit AS CustoUnitario

FROM dbo.HARDWARE INNER JOIN

dbo.ADServico_User ON dbo.HARDWARE.UserID = dbo.ADServico_User.UserID INNER JOIN

dbo.SERVICO ON dbo.ADServico_User.GrupoServico = dbo.SERVICO.S_GrupoServico INNER JOIN

dbo.ModeloPC ON dbo.HARDWARE.MODELO_ID = dbo.ModeloPC.MODELO_ID INNER JOIN

dbo.ModeloPC_Tipo ON dbo.ModeloPC.MOD_Tipo = dbo.ModeloPC_Tipo.MOD_ID

WHERE (dbo.HARDWARE.MONITOR_ID <> 5)

GROUP BY dbo.SERVICO.S_NomeDir, dbo.ModeloPC_Tipo.MOD_CustoUnit, dbo.ModeloPC_Tipo.MOD_Nome

HAVING (dbo.SERVICO.S_NomeDir = @.Direccao)

) SubQUery

UNION

SELECT Item, Unidades, CustoUnitario, Unidades*CustoUnitario AS Total

FROM

(

SELECT dbo.Monitor.MON_Monitor AS Item, COUNT(*) AS Unidades, dbo.Monitor.MON_CustoUnitario AS CustoUnitario

FROM dbo.HARDWARE INNER JOIN

dbo.ADServico_User ON dbo.HARDWARE.UserID = dbo.ADServico_User.UserID INNER JOIN

dbo.SERVICO ON dbo.ADServico_User.GrupoServico = dbo.SERVICO.S_GrupoServico INNER JOIN

dbo.Monitor ON dbo.HARDWARE.MONITOR_ID = dbo.Monitor.MONITOR_ID

WHERE (dbo.HARDWARE.MONITOR_ID =1 OR dbo.HARDWARE.MONITOR_ID=2) AND dbo.SERVICO.S_NomeDir=@.Direccao

GROUP BY dbo.Monitor.MON_Monitor, dbo.Monitor.MON_CustoUnitario

) SubQUery

END

OUTPUT:
Desktops 166 433,09 71892,94
Portáteis 3 675,84 2027,52
TFT 15 166 22,08 3665,28
TFT 17 3 24,35 73,05

How can I SUM the last Column of the 2 queries? How can I SUM (Total)?

Thanks!!!

|||

--1.SUM the last two columns

SELECT t.Item, t.Unidades, (t.CustoUnitario+t.Total) AS LAST2Sum FROM (Your UNION result) t

--2.SUM your TOTAL

SELECT SUM(t.Total) AS SumTotal FROM (Your UNION result) t