Combine 2 aggregate queries into one select statement? 2

[JAFrank]

I have 2 queries which perform aggregates (sum) on the same column, depending on the value of a different column in the same table - similar to this:

SELECT SUM(TotalCount) WHERE PaperSize = "Letter"
SELECT SUM(TotalCount) WHERE PaperSize <> "Letter"

The actual queries are slightly more involved than that, but the idea is to return two values - a total where paper size is Letter and a total where paper size is other than Letter.

Is it possible to combine these 2 queries into a single query that returns both values?

I am trying to find ways to make my code more efficient, and it seems like one query ought to be more efficient than two.

Thanks!

 

[Zografski]

SELECT SUM(CASE PaperSize WHEN 'Letter' THEN TotalCount ELSE 0 END) AS PaperSize,
SUM(CASE PaperSize WHEN 'Letter' THEN 0 ELSE TotalCount END) AS NoPaperSize

Testing displays the presence, not the absence of bugs.
If a software application has to be designed, it has to be designed correctly!

 

[gmmastros]

Select Sum(Case when PaperSize = 'Letter' Then TotalCount Else 0 End) As LetterCount,
       Sum(Case When PaperSize = 'Letter' Then 0 Else TotalCount End) As NonLetterCount

And, yes, this will be more efficient than having 2 separate queries.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[JAFrank]

Thanks for this.

I have never used Case in SQL, so I would like to make sure that I am clear on what this is doing:

Select Sum(Case when PaperSize = 'Letter' Then TotalCount Else 0 End) As LetterCount,
       Sum(Case When PaperSize = 'Letter' Then 0 Else TotalCount End) As NonLetterCount

So, this is going to return 2 values - "LetterCount", and "NonLetterCount"?
In the case of LetterCount, it is going to Sum TotalCount when PaperSize = Letter or return 0 when it's not?

My database comes from a copier's history log. Each row represents the attributes of a copy/print job. The row will always include a page count and a paper size. The data that I am gathering amounts to: "During the reporting period, how many pages were printed on Letter sized paper and how many pages were printed on paper other than Letter sized?"

I can't think of any situation where I would expect the output to be zero, unless the sum of one of those situations actually was zero.

Or am I misunderstanding the purpose of the 0 in the code?

I really appreciate the help, I just need to understand how stuff works in order to learn from it.

Thanks,

JAFrank

 

[Qik3Coder]

The zero is so that you don't get weirdness with null values.
In something like a sum you shouldn't have a problem, but if you were doing string concattenation with a case statement you always want an ELSE '' END, so that you don't wind up with a null in there. That will clear the entire string.

FYI, You can "stack" a case when like a switch in c based languages

Case
WHEN 'Letter' Then '8 1/2x11'
WHEN 'Legal' THEN '8 1/2x14'
WHEN 'HUGE' THEN '24x48'
ELSE '?' END PaperDimensions

Lodlaiden

You've got questions and source code. We want both!

 

[JAFrank]

Ok, cool.

And then in the second example, "NonLetterCount", we are using

"then 0 else TotalCount"

rather than

"<> "Letter"?

It looks like 2 ways to achieve the same result?

 

[Qik3Coder]

Correct.
Some people prefer positive/explicit based comparisions whereas other prefer concise comparisons.

I do a lot of
if([!]![/!]String.IsNullOrEmpty(str))
...

rather than
if(String.IsNullOrEmpty(str) == false)
...

You've got questions and source code. We want both!

 

[JAFrank]

Thanks for the explanation.

Since I have one of those jobs where I don't get to do any one thing regularly, and have to jump from skillset to skillset (and language to language when I DO get to code), I tend to gravitate toward syntax that will be easy for me to understand when I next look at it - in 6 months or a year.

It doesn't always make for compact code, but the extra typing now saves a ton of "What the heck was I trying to do there" down the road.

Thanks again!

 

[gmmastros]

Sorry for the slow reply. Lod is correct in that the code I suggested is meant to accommodate NULLS in the PaperSize column.

Let's image we have a table like this:

[tt]
PaperSize TotalCount
--------- ----------
Letter 10
Letter 20
Small 100
Small 200
Big 300
NULL 1000
[/tt]

total letter count should be 30, right? What about the non-letter count? You probably think it should be 1600 (because the NULL PaperSize should be included).

To explain this, let's write some code. To follow the examples, open a query window in SQL Server Management Studio and copy/paste the code blocks to see how the code works.

The following code sets up a table variable and hard codes some data. This allows us to play with the query without actually affecting any data you have in any of your tables.

Declare @Temp Table(PaperSize VarChar(20), TotalCount int)

Insert Into @Temp Values('Letter',10)
Insert Into @Temp Values('Letter',20)
Insert Into @Temp Values('Small', 100)
Insert Into @Temp Values('Small', 200)
Insert Into @Temp Values('Big',   300)
Insert Into @Temp Values(NULL,    1000)

Now, let me show you how the case statements affect the data. When thinking about case statements, you should think of them as operating on a row-by-row basis.

Declare @Temp Table(PaperSize VarChar(20), TotalCount int)

Insert Into @Temp Values('Letter',10)
Insert Into @Temp Values('Letter',20)
Insert Into @Temp Values('Small', 100)
Insert Into @Temp Values('Small', 200)
Insert Into @Temp Values('Big',   300)
Insert Into @Temp Values(NULL,    1000)

Select PaperSize, 
       TotalCount,
       Case When PaperSize = 'Letter' Then TotalCount Else 0 End As LetterColumn,
       Case When PaperSize = 'Letter' Then 0 Else TotalCount End As NonLetterColumn,
       Case When PaperSize <> 'Letter' Then TotalCount Else 0 End As NotLetter
From   @Temp

When you run the code above, you will get:

[tt][blue]

PaperSize TotalCount LetterColumn NonLetterColumn NotLetter
-------------------- ----------- ------------ --------------- -----------
Letter 10 10 0 0
Letter 20 20 0 0
Small 100 0 100 100
Small 200 0 200 200
Big 300 0 300 300
NULL 1000 0 1000 [!]0[/!]
[/blue][/tt]

Notice how we get 0's for some of the case statements and actual values for other case statements. Also notice the single piece of data highlighted in red above. This is the case statement that uses PaperSize <> 'Letter'. You see, the PaperSize is NULL for that row and NULL cannot be compared with a value, so it is not included in the case and therefore returns 0.

Next, I hope you can see that summing the values from the output of the case statements will return the data you are looking for. So...

Declare @Temp Table(PaperSize VarChar(20), TotalCount int)

Insert Into @Temp Values('Letter',10)
Insert Into @Temp Values('Letter',20)
Insert Into @Temp Values('Small', 100)
Insert Into @Temp Values('Small', 200)
Insert Into @Temp Values('Big',   300)
Insert Into @Temp Values(NULL,    1000)

Select Sum(Case When PaperSize = 'Letter' Then TotalCount End) As LetterCount,
       Sum(Case When PaperSize <> 'Letter' Then TotalCount End) As NonLetterCount,
       Sum(Case When PaperSize = 'Letter' Then 0 Else TotalCount End) As AllNonLetterCount
From   @Temp

The query shown above returns:

[tt][blue]
LetterCount NonLetterCount AllNonLetterCount
----------- -------------- -----------------
30 600 1600
[/blue][/tt]

Notice the column labelled NonLetterCount and the other one AllNonLetterCount. Depending on your data, it may not be possible for those columns to return different values. The only way to get different values from these columns is if there are NULLs in the PaperSize column. If your table does not allow nulls, or there simply aren't any nulls, then the NonLetterCount and the AllNonLetterCount columns will return the same value.

Does this help you to understand the query?



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[JAFrank]

Thanks for the explanation.

Luckily, nulls aren't a factor in this data. Each record will have *something* in each field, so all I have to worry about is calculating a "total if this" and a "total if that".

It's working now, using two queries, and has been for a month or so - but something in me grates at the idea of using two commands where one should do.

JAFrank