how to group the amount billed per currency (currency rate) and per month?

Question

how to group the amount billed per currency (currency rate) and per month?

Navigation

#1 by (0 votes)

0

I have the following query:

select sh.CurrencyRateID, cr.ToCurrencyCode, 
       sum(TotalDue) as [cantidad facturada], month(OrderDate) as mes
from sales.SalesOrderHeader sh 
     join sales.CurrencyRate cr 
         on sh.CurrencyRateID=cr.CurrencyRateID
group by sh.CurrencyRateID, cr.ToCurrencyCode, month(OrderDate)
order by sh.CurrencyRateID asc, 2 desc, 4

The output is as follows,

but now I do not know how to group the currency type, so that it only comes out once, and shows the total

sql

asked by m3nt0r1337 26.06.2016 в 06:21

source

1 answer

Calculate php major date Does not show the image hosted in the SQL Server database

score 0 · Accepted Answer

This happens because the grouping is given by all the columns of the GROUP BY and consequently as the column CurrencyRateID has 4 different values for the value AUD of the column ToCurrencyCode , it is separated in these 4 "sub groups"

You simply need to remove the% column from% from the grouping

SELECT cr.ToCurrencyCode, 
       SUM(TotalDue) as [cantidad facturada], 
       MONTH(OrderDate) as mes
   FROM sales.SalesOrderHeader sh 
       JOIN sales.CurrencyRate cr 
            ON sh.CurrencyRateID = cr.CurrencyRateID
   GROUP BY cr.ToCurrencyCode, MONTH(OrderDate)

If you also want to delete the month column, you can do two things.

One, delete CurrencyRateID of GROUP BY and SELECT, so you will have the total sum of all months.

The other is filtering for a specific month and you will have the total sum of a given month; This is optional, removing the row from WHERE alanza to get the historical total.

SELECT cr.ToCurrencyCode, 
       SUM(TotalDue) as [cantidad facturada]
   FROM sales.SalesOrderHeader sh 
       JOIN sales.CurrencyRate cr 
            ON sh.CurrencyRateID = cr.CurrencyRateID
   WHERE MONTH(OderDate) = 7
   GROUP BY cr.ToCurrencyCode