This week's book giveaway is in the OO, Patterns, UML and Refactoring forum. We're giving away four copies of Refactoring for Software Design Smells: Managing Technical Debt and have Girish Suryanarayana, Ganesh Samarthyam & Tushar Sharma on-line! See this thread for details.
this (below) doesn't work. I want to SUM the content in all D's
Your outer select needs a GROUP BY CITY too, in order to make this approach work:
select city, SUM(D1+D2+D3+D4+D5) as DS from ( ... ) GROUP BY CITY
But, as I'll show below, you can do this without the inline view.
First though, get rid of all those DISTINCTs:
count(distinct case when type = 'D(1)' then '1' end ) "D1"
Do you realize that this expression will only ever resolve to 0 or 1? If there are no rows where type = 'D1' it resolves to 0. If there is 1 or more row where type = 'D1' it resolves to 1, regardless of the number of rows. Think of it this way: you're essentially saying something like:
select count( distinct 'constantExpression' ) from all_tables
I'm guessing that's not what you intend.
Ok, back to easier ways to sum the Ds. You have numerous syntax choices, stll using case when...end.
Or, what I proposed in the original thread on this topic
Now, having said all that, using case when...end is great for throwing together ad hoc queries based on codes. But the fact that you want to report on "all the Ds" suggests to me that you've imbedded logic into your codes: D(1), D(2), T(1), T(2), etc.
A classy way of reporting on data like this is to encode these relationships into a table and join to that table in your query:
Then you use the values of reportingType instead of building those groups in the query.
Joined: Sep 10, 2004
"Do you realize that this expression will only ever resolve to 0 or 1? If there are no rows where type = 'D1' it resolves to 0. If there is 1 or more row where type = 'D1' it resolves to 1, regardless of the number of rows. Think of it this way: you're essentially saying something like:"