[SOLVED] SQL Select from 3 tables with sum

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?

Solution

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