CODEWITHSUNDEEP
sqlsql-server
CAR
CAR

Reputation: 155

SQL YTD for previous years and this year

Wondering if anyone can help with the code for this. I want to query the data and get 2 entries, one for YTD previous year and one for this year YTD.

Only way I know how to do this is as 2 separate queries with where clauses.. I would prefer to not have to run the query twice.

One column called DatePeriod and populated with 2011 YTD and 2012YTD, would be even better if I could get it to do 2011YTD, 2012YTD, 2011Total, 2012Total... though guessing this is 4 queries.

Thanks

EDIT: In response to help clear a few things up: This is being coded in MS SQL.

The data looks like so: (very basic example)

Date       |   Call_Volume
1/1/2012   |   4

What I would like is to have the Call_Volume summed up, I have queries that group it by week, and others that do it by month. I could pull all the dailies in and do this in Excel but the table has millions of rows so always best to reduce the size of my output.

I currently group by Week/Month and Year and union all so its 1 output. But that means I have 3 queries accessing the same table, large pain, very slow not efficient and that is fine but now I also need a YTD so its either 1 more query or if I could find a way to add it to the yearly query that would ideal:

So

DatePeriod   |   Sum_Calls
2011 Total   |   40
2011 YTD     |   12
2012 Total   |   45
2012 YTD     |   15

Hope this makes any sense.

Upvotes: 2

Views: 3838

Answers (1)

VoteyDisciple
VoteyDisciple

Reputation: 37813

SQL is built to do operations on rows, not columns (you select columns, of course, but aggregate operations are all on rows).

The most standard approach to this is something like:

SELECT SUM(your_table.sales), YEAR(your_table.sale_date)
FROM your_table
GROUP BY YEAR(your_table.sale_date)

Now you'll get one row for each year on record, with no limit to how many years you can process. If you're already grouping by another field, that's fine; you'll then get one row for each year in each of those groups.

Your program can then iterate over the rows and organize/render them however you like.

If you absolutely, positively must have columns instead, you'll be stuck with something like this:

SELECT SUM(IF(YEAR(date) = 2011, sales, 0)) AS total_2011,
  SUM(IF(YEAR(date) = 2012, total_2012, 0)) AS total_2012
FROM your_table

If you're building the query programmatically you can add as many of those column criteria as you need, but I wouldn't count on this running very efficiently.

(These examples are written with some MySQL-specific functions. Corresponding functions exist for other engines but the syntax would be a little different.)

Upvotes: 1

Related Questions