Order by Month and Year in SQL with Sum

Order By month and year in sql with sum

Try this:

SELECT     { fn MONTHNAME(OrderDate) } AS MonthName, YEAR(OrderDate) AS Year, SUM(TotalValue) AS Profits
FROM         [Order]
WHERE     (YEAR(OrderDate) = @year)
GROUP BY { fn MONTHNAME(OrderDate) }, MONTH(OrderDate), YEAR(OrderDate)
order by Year(orderDate),month(OrderDate)

Note you need to add any fields you are ordering by to the group by clause

SQl query to sum-up amounts by month/year

select year(date) as y, month(date) as m, sum(paid) as p
from table
group by year(date), month(date)

SQL Server query to get total against each month of a year and return the year and month in date format not the string format

Dates have no formats, they are binary values, just like int or decimal or binary. DATE_FORMATION should be a date type, eg date, datetime or datetime2. If the column uses the correct type, the query can be simplified to just :

select
    datefromparts(Year(DATE_FORMATION),MONTH(DATE_FORMATION),1) as DATE_FORMATION,  
    count([CO_ID]) as CO,
    sum([TOTAL_MEMBERS]) as Total_Members
from COCores
where 
    [DATE_FORMATION] as date) >= DATEFROMPARTS(Year(current_timestamp),1,1)
group by 
    YEAR(DATE_FORMATION),
    MONTH(DATE_FORMATION)

DATEFROMPARTS(Year(current_timestamp),1,1) returns the first date of the current year. This is used to return only rows in the current year. The results are grouped by the Year and Month of DATE_FORMATION using the YEAR and MONTH functions. Finally, SELECT returns the first day of each month by using DATEFROMPARTS to generate a date value from the group keys

Queries like this become far easier if you use a Calendar table. A calendar table contains rows for each date eg for 50 years and extra columns for year, month, day, week number, names, etc that can be used to make reporting easier. Calendar tables are heavily indexed, allowing easy querying eg by year, quarter, semester etc.

Assuming DATE_FORMATION is a date, and a Calendar table with date, Year, Month and StartOfMonth columns, you could turn the query into :

select
    Calendar.StartOfMonth,  
    count([CO_ID]) as CO,
    sum([TOTAL_MEMBERS]) as Total_Members
from COCores inner join Calendar on DATE_FORMATION=Calendar.date
where 
    Calendar.Year= Year(current_timestamp)
group by 
    StartOfMonth

This query would be fast too as it can take advantage of any indexes on Year, Date and StartOfMonth

SQL grouping by month and year

SELECT CAST(MONTH(date) AS VARCHAR(2)) + '-' + CAST(YEAR(date) AS VARCHAR(4)) AS Mjesec, SUM(marketingExpense) AS SumaMarketing, SUM(revenue) AS SumaZarada 
FROM [Order]
WHERE (idCustomer = 1) AND (date BETWEEN '2001-11-3' AND '2011-11-3')
GROUP BY CAST(MONTH(date) AS VARCHAR(2)) + '-' + CAST(YEAR(date) AS VARCHAR(4))

Or as @40-Love mentioned you can cast with leading zeroes:

GROUP BY 
  CAST(YEAR(date) AS VARCHAR(4)) + '-' + right('00' + CAST(MONTH(date) AS VARCHAR(2)), 2) 

Order By month and year in ORACLE with sum

TO_CHAR(TO_DATE(DATE),'MonthYYYY')

There are couple of things incorrect.

Never apply TO_DATE on a DATE column. It forces Oracle to:

• first convert it into a string
• then convert it back to date

based on the locale-specific NLS settings. You need TO_DATE to convert a literal into date. For date-arithmetic, leave the date as it is.

You are currently sorting the string, since TO_CHAR converts the date into string.

You could do it as:

SQL> alter session set nls_date_format = 'YYYY-MON-DD';

Session altered.

SQL> SELECT TRUNC(hiredate,'MONTH')
  2  FROM emp
  3  GROUP BY TRUNC(hiredate,'MONTH')
  4  ORDER BY TRUNC(hiredate,'MONTH');

TRUNC(HIRED
-----------
1980-DEC-01
1981-FEB-01
1981-APR-01
1981-MAY-01
1981-JUN-01
1981-SEP-01
1981-NOV-01
1981-DEC-01
1982-JAN-01
1982-DEC-01
1983-JAN-01

11 rows selected.

NOTE : The alter session statement is only to display the date in desired format.

SQL : given a year and month how can I count the number of orders per WEEK

You can use CASE to get a single week number then group by that number.

Use CROSS APPLY (VALUES to avoid repeating code

SELECT
    v.WeekNumber,
    TotalOrders = COUNT(*)
FROM
    dbo.Orders
CROSS APPLY (VALUES (
    CASE WHEN DateCreated >= DATEFROMPARTS(@year, @month, 1) AND DateCreated < DATEFROMPARTS(@year, @month, 8)
         THEN 1
         WHEN DateCreated >= DATEFROMPARTS(@year, @month, 8) AND DateCreated < DATEFROMPARTS(@year, @month, 15)
         THEN 2
         WHEN DateCreated >= DATEFROMPARTS(@year, @month, 15)) AND DateCreated < DATEFROMPARTS(@year, @month, 22)
         THEN 3
         WHEN DateCreated >= DATEFROMPARTS(@year, @month, 22)) AND DateCreated < DATEFROMPARTS(@year, @month, 29)
         THEN 4
         ELSE 5
    END
)) v(WeekNumber)

WHERE DateCreated >= DATEFROMPARTS(@year, @month, 1)
  AND DateCreated < DATEADD(month, 1, DATEFROMPARTS(@year, @month, 1))
GROUP BY
  v.WeekNumber;

Note the use of >= AND < for a half-open interval. This ensure that the whole of the last day is included.

---

Related Topics