This fascinating exchange led me to the following query which will work against this table to return the next 7 rows after the first 4 rows.
select top(7) * FROM [AAC\vafscjaesct].Whatever
WHERE Id NOT IN (
SELECT top 4 Id FROM [AAC\vafscjaesct].Whatever
ORDER BY DataPoint, SomethingElse
)
ORDER BY DataPoint, SomethingElse
Using the table and its data as shown here I can run the following commands to get sums and averages off of the DataPoint column which contains decimal values. Observe:
SELECT SUM(DataPoint) AS SumOfData FROM [AAC\vafscjaesct].Whatever
The above SQL returns 58. This is a sum of all 12 rows. An average of 4.833333 (again across all 12 rows) is brought back from this query:
SELECT AVG(DataPoint) AS AvgOfData FROM [AAC\vafscjaesct].Whatever
What if we wish to get separate sums and averages for separate chunks of the dozen rows? The following two queries show how that is done and also the table that comes back in each case detailing the breakdown. The rows are categorized based upon the SomethingElse column in this example. Imagine how something comparable might be done for a view "over top" of a table. One could have the line items of orders for a shopping cart in a table and then order summaries for the individual orders summarized in a view. In this scenario, the line item price values could be summed and grouped by individual orders.
SELECT SomethingElse, SUM(DataPoint) AS SumOfData
FROM [AAC\vafscjaesct].Whatever
GROUP BY SomethingElse
| foo | 3 |
| bar | 9 |
| baz | 22 |
| qux | 24 |
Another example:
SELECT SomethingElse, AVG(DataPoint) AS AvgOfData
FROM [AAC\vafscjaesct].Whatever
GROUP BY SomethingElse
| foo | 1.500000 |
| bar | 3.000000 |
| baz | 5.500000 |
| qux | 8.000000 |
Follow the declaration for the guid id column with DEFAULT newid() when crafting a new table in MSSQL to make the column automatically populate guids upon record insertion. The presence of this code snippet allows us to run insert statements against the table specified below without having to insert guids for the id column. Instead, the guids are generated for us. Otherwise, the insertion would fail as otherwise we would need to specifically insert guids into the Id column when inserting records.
BEGIN TRANSACTION
GO
CREATE TABLE [AAC\vafscjaesct].Whatever
(
Id uniqueidentifier NOT NULL DEFAULT newid(),
DataPoint decimal(3, 0) NOT NULL,
SomethingElse varchar(3) NOT NULL
) ON Form224Data
GO
ALTER TABLE [AAC\vafscjaesct].Whatever ADD CONSTRAINT
PK_Whatever PRIMARY KEY CLUSTERED
(
Id
) WITH( STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON) ON Form224Data
GO
ALTER TABLE [AAC\vafscjaesct].Whatever SET (LOCK_ESCALATION = TABLE)
GO
INSERT INTO [AAC\vafscjaesct].Whatever (DataPoint, SomethingElse) Values (1,'foo')
INSERT INTO [AAC\vafscjaesct].Whatever (DataPoint, SomethingElse) Values (2,'foo')
INSERT INTO [AAC\vafscjaesct].Whatever (DataPoint, SomethingElse) Values (2,'bar')
INSERT INTO [AAC\vafscjaesct].Whatever (DataPoint, SomethingElse) Values (3,'bar')
INSERT INTO [AAC\vafscjaesct].Whatever (DataPoint, SomethingElse) Values (4,'bar')
INSERT INTO [AAC\vafscjaesct].Whatever (DataPoint, SomethingElse) Values (4,'baz')
INSERT INTO [AAC\vafscjaesct].Whatever (DataPoint, SomethingElse) Values (5,'baz')
INSERT INTO [AAC\vafscjaesct].Whatever (DataPoint, SomethingElse) Values (6,'baz')
INSERT INTO [AAC\vafscjaesct].Whatever (DataPoint, SomethingElse) Values (7,'baz')
INSERT INTO [AAC\vafscjaesct].Whatever (DataPoint, SomethingElse) Values (7,'qux')
INSERT INTO [AAC\vafscjaesct].Whatever (DataPoint, SomethingElse) Values (8,'qux')
INSERT INTO [AAC\vafscjaesct].Whatever (DataPoint, SomethingElse) Values (9,'qux')
COMMIT
http://msdn.microsoft.com/en-us/library/ff648951.aspx asserts: The Microsoft Enterprise Library is a collection of reusable software components (application blocks) designed to assist software developers with common enterprise development cross-cutting concerns...
