CODEWITHSUNDEEP
sql-server-2008sapb1
Kinyanjui Kamau
Kinyanjui Kamau

Reputation: 1936

SQL Select from 3 tables with sum

I am using Microsoft SQL Server 2008 R2. I want to join 3 tables and then group them by period (months) and get the sum of all amounts for each period and maximum exchange rate. I have this SQL statement below.

SELECT T2.Name AS Period, T1.LineTotal AS Amount, T0.DocRate AS ExchangeRate
FROM OINV T0 INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry 
INNER JOIN dbo.OFPR AS t2 ON T1.FinncPriod = T2.AbsEntry 
WHERE T0.DocStatus = 'O'

Example results from the above query:

Period          Amount      ExchangeRate
Year 2012-01    500.000000  83.000000
Year 2012-01    1000.000000 1.000000
Year 2012-02    2000.000000 1.000000
Year 2012-02    3000.000000 1.000000
Year 2012-02    2500.500000 1.000000
Year 2012-03    500.000000  1.000000
Year 2012-03    1500.000000 94.000000
Year 2012-04    4000.000000 1.000000
Year 2012-04    2000.000000 1.000000
Year 2012-04    2000.000000 1.000000

I would like to get the following results:

Period          Amount      ExchangeRate
Year 2012-01    1500.000000 83.000000
Year 2012-02    7500.000000 1.000000
Year 2012-03    2000.000000 94.000000
Year 2012-04    8000.000000 1.000000

How do I modify my query to get the above results?

Upvotes: 0

Views: 153

Answers (2)

Joe G Joseph
Joe G Joseph

Reputation: 24046

try this:

Just do Group by

SELECT   T2.NAME AS PERIOD
       , SUM(T1.LINETOTAL) AS AMOUNT
       , MAX(T0.DOCRATE) AS EXCHANGERATE
FROM   OINV T0 INNER JOIN INV1 T1 
       ON T0.DOCENTRY = T1.DOCENTRY 
       INNER JOIN DBO.OFPR AS T2 
       ON T1.FINNCPRIOD = T2.ABSENTRY 
WHERE T0.DOCSTATUS = 'O' 
GROUP BY T2.NAME

Upvotes: 1

Adriaan Stander
Adriaan Stander

Reputation: 166406

Try using SUM, MAX and GROUP BY.

Something like

SELECT  T2.Name AS Period, 
        SUM(T1.LineTotal) AS Amount, 
        MAX(T0.DocRate) AS ExchangeRate 
FROM    OINV T0 INNER JOIN 
        INV1 T1 ON T0.DocEntry = T1.DocEntry  INNER JOIN 
        dbo.OFPR AS t2 ON T1.FinncPriod = T2.AbsEntry  
WHERE   T0.DocStatus = 'O'  
GROUP BY    T2.Name

Upvotes: 1

Related Questions