Problem on query operation

Hello, I have my table Produits :

CREATE TABLE [dbo].[Produit] (
[Produit_ID] [int]IDENTITY (1, 1)NOT NULL ,
[Reference] [nvarchar] (50) COLLATE French_CI_ASNULL ,
[Designation] [nvarchar] (50) COLLATE French_CI_ASNULL ,
[Quantite] [int]NULL ,
[PrixU] [sql_variant]NULL ,
[MontantHT] [sql_variant]NULL ,
[TVA] [sql_variant]NULL ,
[Facture_ID] [int]NOT NULL
)
GO
here is stored procedure

 CREATE PROC spBaseTVA_Bis( @.Facture_IDint )AS SELECTSUM(MontantHT)AS montantHT, TVAFROM ProduitGROUP BY TVA, Facture_IDHAVING ( Facture_ID = @.Facture_ID)GOMy stored procedure  fill data in a datagrid with to column  TauxTVA and TVA  like this :
    TauxTVA      TVA
 
But I would like to add a theard column as (the value of the fisrt colum) *  ( the value of the second colum )
    
I would like to desplay data like this  :
    
     TauxTVA      TVA    Prod
       X           Y     X*Y
      
How can I modify my stored procedure to perform this ?
    
Regards
 

Hello,

This one will work with your table definition.

SELECTSUM(convert(int,MontantHT))AS montantHT,convert(int,TVA)as TVA,SUM(convert(int,MontantHT))*convert(int,TVA)as newColFROM

Produit

WHERE

( Facture_ID= @.Facture_ID)

GROUP

BY TVA, Facture_ID

However, I don't think the data types you defined here are approprite. In stead of sql_variant, you can redefine them to something in your case, such as int, float... for your calculations later on.

|||

Thank you !

This work very well;

But it possible to perform this :

TauxTVA TVA Prod
X Y X*Y

Z T Z*T

And in another column X +Z AND X*Y + Z*T

|||

You can use the previous result as a derived table and run a sum on top of it like this:

SELECT

SUM(a.montantHT)as Sum_montantHT,SUM(a. newCol)as new_SumFROM(SELECTSUM(convert(int,MontantHT))AS montantHT,convert(int,TVA)as TVA,SUM(convert(int,MontantHT))*convert(int,TVA)as newColFROM

Produit

WHERE

(

Facture_ID= 1)

GROUP

BY

TVA, Facture_ID)AS a