Need help with query where i need to aggregate an aggregate
-
Try this
Declare @cols nvarchar(Max);
Declare @query nvarchar(Max);
Declare @tier tinyint;
Set @cols =
STUFF
(
(select ',' + Quotename(a.lbl)
from
(
select distinct DATEADD(DAY, -DAY(timestamp)+1, timestamp) ord
, 'Tier 1 GB - '+ cast(DATENAME(month,timestamp) as NCHAR(3)) + ' ' + Right(cast(Year(timestamp) as char(4)),2) lbl
from #storage a
) a
order by a.ord asc
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') ,1,1, '');
set @query =N'
select p.name
, ' + @cols + '
from
(
select a.name, a.size
,''Tier 1 GB - '' + cast(DATENAME(month,timestamp) as NCHAR(3)) + '' '' + Right(cast(Year(timestamp) as char(4)),2) lbl
from #storage a
, #customer b
where a.customer = b.id
and tier = @tier
) a
pivot
(
max(size)
For lbl in (' + @cols + ')
)p ;'
set @tier = 1;
exec sp_executesql @query , N'@tier tinyint', @tier;Let me know if you have any questions.
-
No, Jeff, an identifier is by its very nature measured on a nominal scale. It is not an interval scale. It is not a ratio scale. It is not a ranking or categorical scale. It names a single entity. It is not a quantity because to quote "The North American Arithmetic; part the third (1834)" numbers are the expression of quantity. This quantity can be either a multitude or a magnitude. If an identifier was a numeric value, it could be diminished or increased. We would not care how it is displayed.
What three times your credit card "number" means? Don't be fooled just because it's a string of digits; that doesn't make it a number. Likewise, representing a number and another system of digits doesn't make it into an identifier.
Can you give me that makes sense numeric value? Do all the arithmetic operations make sense on it? Notice that this does not include check digits and hashing. These algorithms have to pull individual digits out from the string that holds the identifier to do the math on them after they been cast to a numeric data type.
Please post DDL and follow ANSI/ISO standards when asking for help.
- jcelko212 32090 wrote:
No, Jeff, an identifier is by its very nature measured on a nominal scale. It is not an interval scale. It is not a ratio scale. It is not a ranking or categorical scale. It names a single entity. It is not a quantity because to quote "The North American Arithmetic; part the third (1834)" numbers are the expression of quantity. This quantity can be either a multitude or a magnitude. If an identifier was a numeric value, it could be diminished or increased. We would not care how it is displayed.
What three times your credit card "number" means? Don't be fooled just because it's a string of digits; that doesn't make it a number. Likewise, representing a number and another system of digits doesn't make it into an identifier.
Can you give me that makes sense numeric value? Do all the arithmetic operations make sense on it? Notice that this does not include check digits and hashing. These algorithms have to pull individual digits out from the string that holds the identifier to do the math on them after they been cast to a numeric data type.
So, by this logic, are you saying I can't use things like 1st, or 23rd, as those ("not-")numbers don't represent a quantity and I can't apply maths expressions to them? I'll start saying things like "I ran in a race, but I finished 6 people after the person that won." (7th to those that "can't" use numbers properly) That way i can ensure your number has a quantity assigned to it...
Just because you have a string that doesn't represent a quantity, doesn't mean you can't use numbers... They have other meanings. It's not Roman times anymore, and we don't write in Roman Numerals. Come join us in the 20th+ century Joe, and start using numbers for thigsg other than maths; you'll find them quite useful.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
>> So, by this logic, are you saying I can't use things like 1st, or 23rd, as those ("not-")numbers don't represent a quantity and I can't apply maths expressions to them? I'll start saying things like "I ran in a race, but I finished 6 people after the person that won." (7th to those that "can't" use numbers properly) That way I can ensure your number has a quantity assigned to it...<<
I think I see your problem. You don't know what a number is! Your ordinals such as 1st or 23rd are positions in the list. They are not numbers no more than A[i,j] cells in some matrix are numbers. They both locate a scalar value in a data structure (list and two-dimensional array respectively)
>> Just because you have a string that doesn't represent a quantity, doesn't mean you can't use numbers... They have other meanings. It's not Roman times anymore, and we don't write in Roman Numerals. <<
You've just confused numbers (an abstraction) with numerals (the symbols used to display them). We can use the Unicode "4" or "IIII" or "IV" in Roman numerals (early vs late empire) or Chinese "?" for display.
If you truly believe there is no difference then please add 1st to 23rd and tell me what meaningful results you get. Ordinals are not quantities or magnitudes, so it makes no sense to add them does it?
Please post DDL and follow ANSI/ISO standards when asking for help.
-
Actually an identifier is numeric (integer) because it just makes no common sense to do otherwise. Overly-pedantic concerns about whether it's used in math or not are actually irrelevant. There are just too many practical issues with storing an id as character data. There's no misinformation here: everyone [well, almost everyone] understands that ids won't have square roots taken of them, and that such a value would be meaningless.
And how the column's values are to be displayed also has no effect on how they should be stored. Leading zeros can be produced for display without being stored. Similarly, dates/times are stored as integer displacements but are not displayed to humans that way. The /, -, :, etc., are not stored, yet are displayed if/when wanted.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
- jcelko212 32090 wrote:
No, Jeff, an identifier is by its very nature measured on a nominal scale. It is not an interval scale. It is not a ratio scale. It is not a ranking or categorical scale. It names a single entity. It is not a quantity because to quote "The North American Arithmetic; part the third (1834)" numbers are the expression of quantity. This quantity can be either a multitude or a magnitude. If an identifier was a numeric value, it could be diminished or increased. We would not care how it is displayed.
What three times your credit card "number" means? Don't be fooled just because it's a string of digits; that doesn't make it a number. Likewise, representing a number and another system of digits doesn't make it into an identifier.
Can you give me that makes sense numeric value? Do all the arithmetic operations make sense on it? Notice that this does not include check digits and hashing. These algorithms have to pull individual digits out from the string that holds the identifier to do the math on them after they been cast to a numeric data type.
Now tell me why I care whether I'll be doing math or not on a number if using a number is the best way to express something?
-
I don't really have any issue with storing cc numbers as char(16), especially since they'll need to be encrypted and end up being stored as binary anyway. I also wouldn't really object to their base data type being bigint either, although I think you can make somewhat more of a case for storing cc as chars.
But ids are a separate category. It just makes no sense to store them as character, it's too much overhead and hassle.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
- jcelko212 32090 wrote:
I think I see your problem. You don't know what a number is!
No, I clearly know what one is; I'm afraid it's just you, Joe, that is in denial of their uses. On the point of not knowing though, please learn what a quote button and other functions are... Your malformed posts really ruin any readability and insight (even if misplaced) you're trying to share to the community.
I assume, Joe, that you also never write a date with a number in it either, and would write today's date as something like "The sixth of August two Thousand and Nineteen"? Obviously you can't add dates together (What would "2019-08-06" + "2019-01-07" be or perhaps "1999-07-09" x "2012-12-16"?) so you can't use numbers. They must be a real problem when you come to databases, as you're forced to use a
varcharto store your dates; and thus completely unreadable for searches. I dread to think what you'd do if you needed a date range.I'd be really intrigued to see a database engine that stores it's dates, etc, with no numerical characters. If you manage to design a working one, you might even get a second user, who also believes that you can only use numerical characters if you're going to do maths. Either way, the underlying framework will very likely be a great achievement.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
>> I assume, Joe, that you also never write a date with a number in it either, and would write today's date as something like "The sixth of August two Thousand and Nineteen"? <<
Again, you're confusing a number with a numeral. I have had to actually spell out dates on occasion for legal documents and even include the phrase "in the year of our Lord", but hopefully that sort of colonial language is going away.
>> Obviously you can't add dates together (What would "2019-08-06" + "2019-01-07" be or perhaps "1999-07-09" x "2012-12-16"?) so you can't use numbers. <<
A calendar is an interval scale. Certain operations make sense on such scales. Remember your basic data modeling course? Scales and measurements? It makes sense to talk about adding or subtracting multiples of the base interval unit (a day). This means I'm perfectly free to do certain limited arithmetic operations on it..
>> They must be a real problem when you come to databases, as you're forced to use a VARCHAR(n) to store your dates; and thus completely unreadable for searches. I dread to think what you'd do if you needed a date range. <<
First of all the ISO 8601 standards are fixed-length strings, so I'm not using VARCHAR(n) for dates and times. Try "foo_date CHAR(11) NOT NULL CHECK foo_date LIKE '[12][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]' for a quick basic constraint. The 8601 standard sorts very nicely and makes ranges pretty easy.
>> I'd be really intrigued to see a database engine that stores it's dates, etc, with no numerical characters. <<
Again you're missing the point about numbers not being the same as numerals. If you want to see something really crazy, look back through old NATO documents. In those days the US military standard used three-letter month abbreviations. Then France joined NATO and wanted to get rid of everything that looked remotely English (the French are fanatics about their language and you can look for to make trouble and a lot of ISO standards), so for a time, the official format included using Roman numerals I thru XII in the dates 🙁
Please post DDL and follow ANSI/ISO standards when asking for help.
-
You waste space and risk ambiguity by storing dashes in a date? Seriously? If for some bizarre reason one insists on storing dates as char, they should be YYYYMMDD. Unambiguous. yyyy-07-04 can be Apr 7 or Jul 4 depending on where you are geographically.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
-
Again you're missing the point about numbers not being the same as numerals
So here's the definition of a numeral, a figure, symbol, or group of these denoting a number.
Seems like a numeral is the same as a number it just looks different but can be used interchangeably . So for the purposes of storage you pick whatever works best which usually will be wait for it, just the number.
-
>> Actually an identifier is numeric (integer) because it just makes no common sense to do otherwise. <<
I'm going to assume that you have seen a Harry Potter movie by now. Do you remember when Harry is heading off to Hogwarts and he gets down to the train station? Platform 9¾ is a fictional train platform located in King's Cross Station in London used in the books. It's a good gag, but it's a lousy identifier. Is it also Platform 9.75? Platform +9.75000? There would be no question about the identifier if it were this string that could be validated with a regular expression.
>> Overly-pedantic concerns about whether it's used in math or not are actually irrelevant. There are just too many practical issues with storing an id as character data. <<
I freely admit to being pedantic. That's why I write standards, audit systems, teach college and write books 🙂
>> There's no misinformation here: everyone [well, almost everyone] understands that ids won't have square roots taken of them and that such a value would be meaningless. <<
Then why do you allow it? SQL is supposed to maintain data integrity. In particular, when a number is truncated or extended, there's no immediate indication that the result is invalid when it's converted to a nominal scale string. But when you truncate a string, you know immediately. To use a real example, consider ZIP Codes. Agawam, MA is '01001' which is not the same as the number 1001. Neither of these two alternatives would fit in a five-character field, validated with a regular expression. Consider 8 Bytes for a BIGINT versus 5 bytes for that ZIP code. I've not done a formal study of this, but my feeling is that most ISO encodings are under 10 bytes and a few larger more common ones are 16 bytes (credit card numbers for example). Brent's Rule "store data the way it's used and use data the way it stored" is a pretty good rule.
And how the column's values are to be displayed also has no effect on how they should be stored. Leading zeros can be produced for display without being stored. Similarly, dates/times are stored as integer displacements but are not displayed to humans that way. The /, -, :, etc., are not stored, yet are displayed if/when wanted.
Please post DDL and follow ANSI/ISO standards when asking for help.
-
>> You waste space and risk ambiguity by storing dashes in a date? <<
When we were creating the ANSI/ISO standards for SQL, the dashes were in the format that we picked. I'm not wasting space; ANSI and ISO are 🙂 What we wanted was one and only one display format that would be easy for machines and immediately readable by a human being. This is the one we picked.
>> If for some bizarre reason one insists on storing dates as char, they should be YYYYMMDD. <<
That's one of the possible formats in ISO 8601. We also have a week-within-year and an ordinal-within-year option. But we don't use them in SQL. Again, when you work on the standards committee for something that is going to be international you will want to settle on one and only one way of doing something instead of giving options and assorted dialects.
Please post DDL and follow ANSI/ISO standards when asking for help.
-
>> Seems like a numeral is the same as a number it just looks different but can be used interchangeably. <<
No, they cannot be used interchangeably. This is the fundamental arithmetic concept that is usually covered in middle school about the time the kids are introduced to Roman numerals versus place valued notations. It gets reintroduced again when we get to octal versus hexadecimal in computer science courses.
Please post DDL and follow ANSI/ISO standards when asking for help.
- jcelko212 32090 wrote:
No, Jeff, an identifier is by its very nature measured on a nominal scale. It is not an interval scale. It is not a ratio scale. It is not a ranking or categorical scale. It names a single entity. It is not a quantity because to quote "The North American Arithmetic; part the third (1834)" numbers are the expression of quantity. This quantity can be either a multitude or a magnitude. If an identifier was a numeric value, it could be diminished or increased. We would not care how it is displayed.
What three times your credit card "number" means? Don't be fooled just because it's a string of digits; that doesn't make it a number. Likewise, representing a number and another system of digits doesn't make it into an identifier.
Can you give me that makes sense numeric value? Do all the arithmetic operations make sense on it? Notice that this does not include check digits and hashing. These algorithms have to pull individual digits out from the string that holds the identifier to do the math on them after they been cast to a numeric data type.
It's funny how you chastise people for using 1950's technology (which was actually awesome and still has seriously good application in the modern world that a lot of today's "kids" under the age of 50 don't grok) and then refer to a book written in 1834 to try to explain your stance. 😀
You really need to come out of your pedantic notions as to what numbers can be used for in databases. They can be used to control fragmentation is wide clustered indexes, act a surrogate keys to protect the natural key(s) of a table from public view and to make changes of the natural keys a non-issue when it comes to relations that depend on the keys, can be used as tie breakers for unique indexes that wouldn't otherwise be unique, and a whole bunch of other things that are based on the math behind integer datatypes.
While I agree with Scott Pletcher that it's stupid to add (for example) an IDENTITY column to every table without reasonable cause (especially if there's a well documented key such as you'll find in the ISO world and world of telephony), there are things that you can do with such numbered indexes (truly "gapless" or not) that you apparently haven't understood. Just because you're famous (I'll give you a compliment by saying that your also "notorious") and have written a lot of books, doesn't mean that 1) you're always right and 2) are well studied/informed. For example, remember "Push Stacks" for converting Adjacency Lists to Nested Sets?
And, yes... I totally agree that IDENTITY columns are usually "nominal" especially since they do result in gaps for failed inserts, etc. But there's a whole lot of mathematical use behind them that you're simply not considering.
p.s. There's a joke riddle in the Navy... "What is Green, has 2 legs, and 14 a$$holes"? The answer is, "the wardroom table". I feel the same way about a lot of so called "Design Committees". Some are good but, more frequently than not, "Design by Committee" ends up producing that which is depicted in the following cartoon...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 15 posts - 16 through 30 (of 30 total)
You must be logged in to reply to this topic. Login to reply