CODEWITHSUNDEEP
sqloracle11g
Usman YousafZai
Usman YousafZai

Reputation: 1120

Query for adding same column entries W.R.T its Foreign Key

I have the following table, with the attributes:

vendor_invoicedetails
   Venid(Pk)
   ven_inv_ref(Fk)
   Item_Code
   Item_Name
   UnitPrice
   VenQuantity

  1. I want to multiply the unitPrice with VenQuantity to have a total price which I did with following query

    select item_code, 
       VEN_INV_REF, 
       unitprice * ven_itemquantity as total
  from vendor_invoicedetails;

  2. The thing I want is to sum the the TotalPrice as Total of two same Ven_inv_Ref(Fk) column.

enter image description here

In the above picture I want sum those entries having same VEN_INV_REF number.

Upvotes: 3

Views: 170

Answers (1)

JensB
JensB

Reputation: 6880

Try this:

DECLARE @datatable TABLE
   (
     ITEM_CODE NVARCHAR(32) PRIMARY KEY CLUSTERED,
     ven_inv_ref NVARCHAR(50),
     Item_Name NVARCHAR(50),
     UnitPrice FLOAT, 
     VenQuantity INT
   )

INSERT INTO @datatable
(ITEM_CODE, ven_inv_ref, UnitPrice, VenQuantity)
VALUES ('battery', 15, 100, 4)

INSERT INTO @datatable
(ITEM_CODE, ven_inv_ref, UnitPrice, VenQuantity)
VALUES ('ABCDE', 16, 200, 4)

INSERT INTO @datatable
(ITEM_CODE, ven_inv_ref, UnitPrice, VenQuantity)
VALUES ('A4', 16, 400, 4)

-- whats in the table   
SELECT * 
FROM @datatable

-- group by reference
SELECT 
    ven_inv_ref, 
    SUM(UnitPrice*VenQuantity) AS totalvalue 
FROM @datatable
GROUP BY ven_inv_ref

Result

enter image description here

Upvotes: 4

Related Questions