So I have a table name "sumTable"
| year | Orders |
|---|---|
| 1996-07-04 00:00:00.000 | 2 |
| 1996-10-15 00:00:00.000 | 3 |
| 1997-10-08 00:00:00.000 | 1 |
| 1996-10-03 00:00:00.000 | 5 |
| 1994-11-05 00:00:00.000 | 1 |
| 1996-07-04 00:00:00.000 | 2 |
| 1999-10-15 00:00:00.000 | 3 |
| 1996-10-08 00:00:00.000 | 1 |
| 1995-10-03 00:00:00.000 | 5 |
| 1996-11-05 00:00:00.000 | 1 |
I want to know the total number of orders in "1996" and to be displayed in a single column,
Expected Output
Total Orders
14
I tried using this
select datepart(yy, '1996'), sum(orders) AS 'Total_Orders' from SumTable group by orders
and this link too get AVG() after GROUP BY in MYSQL
Please advice a solution!
CodePudding user response：
For your requirement you want a single row for the sum of all qualifying rows - you are not grouping anything so there is no need for group by.
Select sum (Orders) as Total_orders
from SumTable
where [year] >='19960101' and [year] < '19970101';
You would want groups of aggregated values if for example you wanted each year and its sum of orders.
Select year([year]) as Order_year, sum(Orders) as Total_orders
from SumTable
group by year([year])
order by Order_year;
Although it's technically okay to have a column named year, it's actually not a year but an Order Date so makes for a somewhat confusing query, I would consider a more meaningful name; this also assumes column year is a datetime data type.
CodePudding user response：
Additional examples to already posted @stu answer.
If you need just one output by specified year the you can use this:
SELECT SUM(Orders) AS Total_orders
FROM SumTable
WHERE YEAR([year]) = 1996
In case you need to sum() of orders by each year:
SELECT DISTINCT YEAR([year]) AS Order_year,
SUM(Orders) OVER (PARTITION BY YEAR([year])) AS Total_orders
FROM SumTable
ORDER BY Order_year
