Sql Sum by Year Report, Looking for an Elegant Solution

SQL sum by year report, looking for an elegant solution

You can try this:

SELECT  T0.ItemCode, 
        SUM(CASE WHEN YEAR(T0.DocDate) = 2011 THEN QUANTITY ELSE 0 END) AS '2011',
        SUM(CASE WHEN YEAR(T0.DocDate) = 2012 THEN QUANTITY ELSE 0 END) AS '2012'
FROM    MyTable T0
GROUP BY 
        T0.ItemCode

Generate year to date by month report in SQL

declare @Q as table 
(
mmonth INT,
value int 
)

insert into @Q
values
(1,10),
(1,12),
(2,45),
(3,23)

select sum(January) as UpToJanuary, 
sum(February)as UpToFebruary,
sum(March) as UpToMarch from (
select 
case when mmonth<=1 then sum(value) end as [January] ,
case when mmonth<=2 then sum(value) end as [February],
case when mmonth<=3 then sum(value) end as [March]
from @Q
group by mmonth
) t

Produces:

UpToJanuary UpToFebruary    UpToMarch
22          67              90

You get the idea, right?

NOTE: This could be done easier with PIVOT tables but I don't know if you are using SQL Server or not.

Group query results by month and year in postgresql

select to_char(date,'Mon') as mon,
       extract(year from date) as yyyy,
       sum("Sales") as "Sales"
from yourtable
group by 1,2

At the request of Radu, I will explain that query:

to_char(date,'Mon') as mon, : converts the "date" attribute into the defined format of the short form of month.

extract(year from date) as yyyy : Postgresql's "extract" function is used to extract the YYYY year from the "date" attribute.

sum("Sales") as "Sales" : The SUM() function adds up all the "Sales" values, and supplies a case-sensitive alias, with the case sensitivity maintained by using double-quotes.

group by 1,2 : The GROUP BY function must contain all columns from the SELECT list that are not part of the aggregate (aka, all columns not inside SUM/AVG/MIN/MAX etc functions). This tells the query that the SUM() should be applied for each unique combination of columns, which in this case are the month and year columns. The "1,2" part is a shorthand instead of using the column aliases, though it is probably best to use the full "to_char(...)" and "extract(...)" expressions for readability.

Creating summary report with selected summary

With SQL:

SELECT
    CustomerType,
    COUNT(*),
    SUM(CASE WHEN Status = 'C' THEN 1 ELSE 0 END),
    SUM(CASE WHEN Status = 'A' THEN 1 ELSE 0 END),
    SUM(Amount)
FROM
    Customers
GROUP BY
    CustomerType
ORDER BY
    CustomerType

SQL query ALL in One - To avoid number of queries

I'd use the IF() and SUM() together as follows:

SELECT SUM(IF(age >= 40,1,0))               AS older40, 
       SUM(IF(age >= 50 and age <= 60,1,0)) AS between50and60
       SUM(IF(age >= 60 and age <= 70,1,0)) AS between60and70
       SUM(IF(age >= 80,1,0))               AS over80

FROM TABLE

Refer to the MySQL explanation for IF(expr1,expr2,expr3), but basically expr1 is the condition, expr2 is the value when the condition is true and expr3 is the else. Using 1 & 0 for these allows the SUM() to count the records.

How can I use SUM for bit columns?

SELECT SUM(CAST(bitColumn AS INT))
FROM dbo.MyTable

need to cast into number

or another solution -

SELECT COUNT(*)
FROM dbo.MyTable
WHERE bitColumn = 1

SQL query for calculating mtd, ytd values

Select t.title, t.Date, 
  Sum(y.Amount) YTD,
  Sum(m.Amount) MTD
From table t
   left join table y
      on y.Title = t.Title
         and datediff(year, y.Date, t.Date) = 0 
         and y.Date <= t.Date
  left join table m
      on m.Title = t.Title
         and datediff(month, m.Date, t.Date) = 0 
         and m.Date <= t.Date
         and m.Date <> y.Date
Group by t.title, t.Date

---

Related Topics