Can I do something in SQL that makes no real sense to be able to do?

December 9, 2008 8:03 AM

SQL Question: can I have a column in a table of results that has it's own restrictive clause.

Say I have a table of products and numbers sold and the date they were sold on, so the table looks like

product|sales|date
widget1|10|2008-11-01
widget2|5|2008-11-01
widget1|8|2008-11-02
widget2|4|2008-11-02

SELECT product, SUM(sales) as total_sales FROM table GROUP BY product

returns

product|total_sales
widget1|50
widget2|40

and

SELECT product, SUM(sales) as december_sales FROM table WHERE date>='2008-12-01' GROUP BY product

returns

product|december_sales
widget1|20
widget2|30

What I want is a query that returns

product|december_sales|total_sales
widget1|20|50
widget2|30|40

Everything tells me this is impossible in SQL and I should just use two queries and handle the results after that (which would be a pain in the neck) but I'm hoping against hope there's some way of doing this.

[I'm using SQL Server 2008 if it makes a difference]

posted by Hartster to Computers & Internet (10 answers total)

Best answer: You should be able to use a CASE statement to have a column that prints the sales if the month is December, and 0 otherwise.

I'm not sure if you can combine that directly with a SUM operator, or if you would need to select from a table that you create with a select inline.
posted by smackfu at 8:19 AM on December 9, 2008

Best answer: If I understand your question correctly:

SELECT
Product as 'Product',
Sum(case when date>='2008-12-01' then Sales Else 0 End) as 'December Sales,
Sum (Sales) as 'Total Sales'
FROM Table
GROUP BY product



on preview - Smackfu beat me to it :)
posted by Lord Widebottom at 8:35 AM on December 9, 2008

It may not be the fastest most optimal SQL, but I think you want to make one of those queries a subquery and join it with the other.
posted by Good Brain at 8:44 AM on December 9, 2008

Previous posters are correct, although the date comparison is flawed come January...

In Oracle syntax (which might or might not apply):

SELECT product,
SUM(CASE WHEN TRUNC(date,'MONTH')=TO_DATE('2008-12','YYYY-MM') THEN sales ELSE 0 END) AS dec_sales,
SUM( sales ) AS all_sales
FROM table
GROUP BY product
;
posted by phax at 8:44 AM on December 9, 2008

Response by poster: Awesome, cheers for that. I completely forgot about Case, despite using it elsewhere. All works nice and smooth.
posted by Hartster at 8:58 AM on December 9, 2008

on second look at what Smackfu did, I think I'm trying to be too fancy
posted by Good Brain at 9:01 AM on December 9, 2008

Best answer: Yeah, the previous answers are on the right track.

But, a diversion: any time you have a "separate query", you can do a subquery. This can be uncorrelated, like this:

select ( select count(*) from foo) as foo_count, ( select count(*) from bar ) as bar_count;

Note that foo and bar have nothing to do with eah other; we're just using two subqueries to jam this all into a single result set. And our main quey, in this example, doesn't query anything. For RDBMSes that complain that the outer select specifies no table, the traditional work-around is to specify the notional table "dual". For some older RDBMSes (e.g, DB/2, in some setups of DB/2) that don't have a dual, the clever hack is to just create a view with no rows, and use that as the outer table.


Or it can be correlated, like this:

select distinct product,
( SUM(sales) as december_sales FROM table WHERE date>='2008-12-01' and product = a.product GROUP BY product ) as december_sales,
( SUM(sales) as december_sales FROM table WHERE product = a.product GROUP BY product ) as ttal_sales,
from table a ;

Now, this is going to be much more expensive in turns of run-time, so it's not the best way to do it. But if you can't cme up with a better way, hey, it works.


Okay, now that we've seen how not to do this if there's a better way, let's find the better way.

It's pretty much what the first two commenters specified, but let's note a few things.

The parameter to sum can be any legal SQL expression that evaluates to a number, not just a column name. So sum( column_name) is legal, but so is sum( 1 ) (as an exercise, what's the result of sum( 1 )?), and so is:
sum( case when column_name = value then numeric_value else numeric_value end ) .

So Lord_Widebottom's solution is right (except for the unterminated string literal).
posted by orthogonality at 9:03 AM on December 9, 2008

Yeah--just on first blush, my thought was to join two subqueries using the Product column as a key, but that approach is only if you need two total columns in your result set, to show both December totals and overall totals side-by-side in your result for comparative purposes.

The pseudo SQL below is all Oracle-style, btw.

This may be a slightly more expensive approach than using CASE statements. But then again, if "Product" is an indexed key column, then joins are pretty cheap. Also, not sure if SQL server supports this kind of aliasing, and the labeling of the "Product" field in the subqueries may be redundant and unnecessary. I'd have to play with it some. (Sorry about lack of indentation or other formatting for readability.)

SELECT DISTINCT Product, December_Sales, Total_Sales
FROM (SELECT Product as Product, SUM(Sales) as December_Sales
FROM table
WHERE date between '2008-12-01' and ‘2008-12-31’
GROUP BY Product) a,
(SELECT Product as Product, Sum(Sales) as Total_Sales
FROM Table
GROUP BY Product) b
WHERE a.Product (+) = b.Product
GROUP BY Product

posted by saulgoodman at 11:25 AM on December 9, 2008

Actually, the "Group By" clause in the outer SQL statement I suggested above isn't necessary either (not sure about the DISTINCT keyword, but it's probably not needed either)...
posted by saulgoodman at 11:29 AM on December 9, 2008

Oh, heh, didn't see phax's take on the CASE statement solution. That's a lot more elegant. So, yeah, do that.
posted by saulgoodman at 11:46 AM on December 9, 2008