Double summation

I have some data -- counts ID'd by location and grid East like this --
Loc East N
CA 100 3
CA 103 5
CA 109 2
CA 110 3

I'm interested in the total of N on either side of the largest gap in
Eastings.
In this case the largest gap is 6 (between 103 and 109), and the sum of
N for the 2 rows below the gap is 8, and for the 2 above the gap it's
5.

The problem is to locate the largest gap, and compute the sum of N for
the cases on either side. There are multiple locations, multiple
Eastings
per location, but only one largest gap. (If there are two largest
gaps, it
does't matter which one is used for the sums.)

I can do this with multiple passes -- first locate the largest gap,
then go
back and locate the Eastings on either side, then sum up the Ns.
That's
realy clumsy, I can't figure out how to do it more quickly, and I'm not
sure
what I'm doing is right. Any help would be appreciated.

Thanks,
Jim GeissmanJim,

CREATE TABLE a(Loc CHAR(2), East INT, N INT)
go
INSERT a VALUES('CA', 100, 3)
INSERT a VALUES('CA', 103, 5)
INSERT a VALUES('CA', 109, 2)
INSERT a VALUES('CA', 110, 3)
INSERT a VALUES('OR', 100, 3)
INSERT a VALUES('OR', 108, 5)
INSERT a VALUES('OR', 109, 2)
INSERT a VALUES('OR', 110, 3)
INSERT a VALUES('WA', 108, 5)
INSERT a VALUES('WA', 109, 2)
INSERT a VALUES('WA', 110, 3)
INSERT a VALUES('WA', 115, 3)

SELECT * FROM(
SELECT Loc, East,
(SELECT SUM(n) FROM a a1 WHERE a.loc = a1.Loc AND a1.East <= a.East)
BeforeGap,
(SELECT SUM(n) FROM a a1 WHERE a.loc = a1.Loc AND a1.East a.East)
AfterGap,
(SELECT MIN(East) FROM a a1 WHERE a.loc = a1.Loc AND a1.East a.East)
- East GapSize,
ROW_NUMBER() OVER(PARTITION BY Loc ORDER BY ((SELECT MIN(East) FROM a
a1 WHERE a.loc = a1.Loc AND a1.East a.East) - East) DESC) rn
FROM a
) t
WHERE rn=1

SELECT * FROM(
SELECT Loc, East,
(SELECT SUM(n) FROM a a1 WHERE a.loc = a1.Loc AND a1.East <= a.East)
BeforeGap,
(SELECT SUM(n) FROM a a1 WHERE a.loc = a1.Loc AND a1.East a.East)
AfterGap,
(SELECT MIN(East) FROM a a1 WHERE a.loc = a1.Loc AND a1.East a.East)
- East GapSize,
ROW_NUMBER() OVER(PARTITION BY Loc ORDER BY ((SELECT MIN(East) FROM a
a1 WHERE a.loc = a1.Loc AND a1.East a.East) - East) DESC) rn
FROM a
) t
WHERE rn=1

Loc East BeforeGap AfterGap GapSize rn
-- ---- ---- ---- ----
-------
CA 103 8 5 6 1
OR 100 3 10 8 1
WA 110 10 3 5 1

(3 row(s) affected)

--------
Alex Kuznetsov
http://sqlserver-tips.blogspot.com/
http://sqlserver-puzzles.blogspot.com/|||Thank you very much, Alex. That's tremendous.
I learned three new things -- row_number, over and partition.
No wonder I was having trouble.

Thanks again,
Jim

Quote:

Originally Posted by

--------
Alex Kuznetsov
http://sqlserver-tips.blogspot.com/
http://sqlserver-puzzles.blogspot.com/

Double Pivot

Hello everybody,
I used to have a cross-tab query which gives me the number of orders
and the total value per year for each customer in the northwind
database. The result looks like this:
Customer #Orders_1996 Value_1996 #Orders_1998 Value_1997
Now I try to rewrite it using the new PIVOT operator. While I succeed
in having the count per year or the sum, I can't figure out how to
combine both aggregates in one query. BOL online aren't much help and
so far in all the articles I haven't seen an example using more than
one.
Does anybody knows if this is possible ?
Thanks MarkusIt is not possible :(
Send an e-mail to sqlwish@.microsoft.com ...
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
<m.bohse@.quest-consultants.com> wrote in message
news:1131479880.917386.253790@.g49g2000cwa.googlegroups.com...
> Hello everybody,
> I used to have a cross-tab query which gives me the number of orders
> and the total value per year for each customer in the northwind
> database. The result looks like this:
> Customer #Orders_1996 Value_1996 #Orders_1998 Value_1997
> Now I try to rewrite it using the new PIVOT operator. While I succeed
> in having the count per year or the sum, I can't figure out how to
> combine both aggregates in one query. BOL online aren't much help and
> so far in all the articles I haven't seen an example using more than
> one.
> Does anybody knows if this is possible ?
> Thanks Markus
>|||m.bo...@.quest-consultants.com wrote:
> Hello everybody,
> I used to have a cross-tab query which gives me the number of orders
> and the total value per year for each customer in the northwind
> database. The result looks like this:
> Customer #Orders_1996 Value_1996 #Orders_1998 Value_1997
> Now I try to rewrite it using the new PIVOT operator. While I succeed
> in having the count per year or the sum, I can't figure out how to
> combine both aggregates in one query. BOL online aren't much help and
> so far in all the articles I haven't seen an example using more than
> one.
> Does anybody knows if this is possible ?
Join the 2 simpler pivot queries?|||Check out the RAC utility for all kinds of static/dynamic
pivoting made easy.
www.rac4sql.net
<m.bohse@.quest-consultants.com> wrote in message
news:1131479880.917386.253790@.g49g2000cwa.googlegroups.com...
> Hello everybody,
> I used to have a cross-tab query which gives me the number of orders
> and the total value per year for each customer in the northwind
> database. The result looks like this:
> Customer #Orders_1996 Value_1996 #Orders_1998 Value_1997
> Now I try to rewrite it using the new PIVOT operator. While I succeed
> in having the count per year or the sum, I can't figure out how to
> combine both aggregates in one query. BOL online aren't much help and
> so far in all the articles I haven't seen an example using more than
> one.
> Does anybody knows if this is possible ?
> Thanks Markus
>|||> Join the 2 simpler pivot queries?
Yes that's an option, but I'm afraid that my final query won't be any
shorter than the original one using CASE statements. And I don't think
it's such an unusual request to have two aggregates (or more) in a
cross-tab report.
>Check out the RAC utility for all kinds of static/dynamic pivoting made easy.[/colo
r]
I checked it out some years ago and wasn't too impressed with it back
then. Also I don't need these kind of things too often, I just created
this query as an example/exercise during classes. But maybe it's time
to have another look at RAC, since there should be a newer version by
now.
Thanks for the comments anyway.
Markus|||<m.bohse@.quest-consultants.com> wrote in message
news:1131528859.873667.16660@.g14g2000cwa.googlegroups.com...
> then. Also I don't need these kind of things too often, I just created
> this query as an example/exercise during classes. But maybe it's time
I have taken the PIVOT slides out of my T-SQL enhancements for SQL
Server 2005 talk. The three groups I showed it to all ended up asking, "can
it do (multiple aggregations, dynamic columns, etc)" -- all things that
would make perfect sense. And the answer in every case was, "no... I guess
it's not really that useful yet... but MS tells me that it WILL BE in a
future version!" So perhaps it will get a place in my T-SQL enhancements
for SQL Server 200x talk :)
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--|||Adam Machanic wrote:
> I have taken the PIVOT slides out of my T-SQL enhancements for SQL
> Server 2005 talk. The three groups I showed it to all ended up asking, "c
an
> it do (multiple aggregations, dynamic columns, etc)" -- all things that
> would make perfect sense. And the answer in every case was, "no... I gues
s
> it's not really that useful yet... but MS tells me that it WILL BE in a
> future version!" So perhaps it will get a place in my T-SQL enhancements
> for SQL Server 200x talk :)
Here is a solution, which I shamelessly plug in from my book
(therefore, the lecturing tone:-)
SQL Server 2005 introduced pivot operator as syntax extension for
table expression in the from clause
select * from
(Sales pivot (sum(Amount) for Month in ('Jan', 'Feb',
'Mar'))
As soon as a new feature is introduced people start wondering if it can
accommodate more complex cases. For example, can we do two aggregations
at once? Given the Sales relation, can we output the sales total
amounts together with sales counts like this
Product JanCnt FebCnt MarCnt JanSum FebSum MarSum
Shorts 1 1 1 20 30 50
Jeans 1 1 1 25 32 37
T-shirt 1 1 10 15
We had to change column names in order to accommodate extra columns
and, if nothing else, the changed column names should hint the
solution. The other idea, which should be immediately obvious from the
way the table columns are arranged in the display, is that the result
is a join between the two primitive pivot queries
Product JanCnt FebCnt MarCnt
Shorts 1 1 1
Jeans 1 1 1
T-shirt 1 1
and
Product JanSum FebSum MarSum
Shorts 20 30 50
Jeans 25 32 37
T-shirt 10 15
Well, what about those fancy column names? There is nothing like JanCnt
in the original data. Indeed, there isn't, but transforming the month
column data into the new column with Cnt postfix is just a string
concatenation. Therefore, the answer to the problem is
select scount.*, ssum.* from (
select * from (
(select product, month || 'Cnt', amount from Sales)
pivot (count(*) for Month in ('JanCnt', 'FebCnt',
'MarCnt')
) scount, (
select * from (
(select product, month || 'Sum', amount from Sales)
pivot (sum(Amount) for Month in ('JanSum', 'FebSum',
'MarSum')
) ssum
where scount.product = ssum.product|||"Vadim Tropashko" <vadimtro_invalid@.yahoo.com> wrote in message
news:1131593567.019132.117700@.g14g2000cwa.googlegroups.com...
> Here is a solution, which I shamelessly plug in from my book
Are you the same Vadim Tropashko who works for Oracle Corp?
If so, why are you writing a SQL Server book? :)

> select scount.*, ssum.* from (
> select * from (
> (select product, month || 'Cnt', amount from Sales)
> pivot (count(*) for Month in ('JanCnt', 'FebCnt',
> 'MarCnt')
> ) scount, (
> select * from (
> (select product, month || 'Sum', amount from Sales)
> pivot (sum(Amount) for Month in ('JanSum', 'FebSum',
> 'MarSum')
> ) ssum
> where scount.product = ssum.product
That's grossly inefficient compared with using SUM(CASE) and COUNT(CASE)
and grouping on the product.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--|||Adam Machanic wrote:
> "Vadim Tropashko" <vadimtro_invalid@.yahoo.com> wrote in message
> news:1131593567.019132.117700@.g14g2000cwa.googlegroups.com...
> Are you the same Vadim Tropashko who works for Oracle Corp?
> If so, why are you writing a SQL Server book? :)
SQL book, not Server:-)

> That's grossly inefficient compared with using SUM(CASE) and COUNT(CAS
E)
> and grouping on the product.
This is true. Although, I fail to see the point of langauge extensions
that can't work seamlessly with the existing features without being
forced to complicate syntax every time a new, slight variation of the
problem arrives. How about pivoting on composite columns, say Month x
Day. Does it require new extension...)

double count

Hello
got a small group by problem, i cant figure out how to divide the count with
"total" count for "each" day :)
CREATE TABLE #Test (
A char(1) NOT NULL,
B char(1) NOT NULL,
Somedate datetime NOT NULL
)
INSERT INTO #Test(A,B)VALUES('A','A','2001-01-01')
INSERT INTO #Test(A,B)VALUES('A','A','2001-01-01')
INSERT INTO #Test(A,B)VALUES('A','B','2001-01-01')
INSERT INTO #Test(A,B)VALUES('A','C','2001-01-01')
INSERT INTO #Test(A,B)VALUES('A','C','2001-01-01')
INSERT INTO #Test(A,B)VALUES('B','A','2001-01-01')
INSERT INTO #Test(A,B)VALUES('B','A','2001-01-01')
INSERT INTO #Test(A,B)VALUES('B','B','2001-01-01')
INSERT INTO #Test(A,B)VALUES('B','A','2001-01-02')
INSERT INTO #Test(A,B)VALUES('B','A','2001-01-02')
INSERT INTO #Test(A,B)VALUES('B','B','2001-01-02')
SELECT * FROM #Test
/* A COUNT COUNT/TOTAL Somedate
A 2 0.40 2001-01-01
A 1 0.20 2001-01-01
A 2 0.40 2001-01-01
B 1 0.50 2001-01-01
B 1 0.50 2001-01-01
B 1 0.50 2001-01-02
B 1 0.50 2001-01-02
*/
DROP TABLE #TestSELECT
T.A,
T.B,
T.SomeDate,
COUNT(*),
(COUNT(*) * 1.0) / T1.theCount
FROM #Test T
JOIN
(
SELECT
A,
COUNT(*) AS TheCount
FROM #Test
GROUP BY A
) T1 ON T1.A = T.A
GROUP BY
T.A,
T.B,
T.SomeDate,
T1.theCount
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Lasse Edsvik" <lasse@.nospam.com> wrote in message
news:OZIvJGnzFHA.908@.tk2msftngp13.phx.gbl...
> Hello
> got a small group by problem, i cant figure out how to divide the count
> with
> "total" count for "each" day :)
>
> CREATE TABLE #Test (
> A char(1) NOT NULL,
> B char(1) NOT NULL,
> Somedate datetime NOT NULL
> )
>
> INSERT INTO #Test(A,B)VALUES('A','A','2001-01-01')
> INSERT INTO #Test(A,B)VALUES('A','A','2001-01-01')
> INSERT INTO #Test(A,B)VALUES('A','B','2001-01-01')
> INSERT INTO #Test(A,B)VALUES('A','C','2001-01-01')
> INSERT INTO #Test(A,B)VALUES('A','C','2001-01-01')
> INSERT INTO #Test(A,B)VALUES('B','A','2001-01-01')
> INSERT INTO #Test(A,B)VALUES('B','A','2001-01-01')
> INSERT INTO #Test(A,B)VALUES('B','B','2001-01-01')
> INSERT INTO #Test(A,B)VALUES('B','A','2001-01-02')
> INSERT INTO #Test(A,B)VALUES('B','A','2001-01-02')
> INSERT INTO #Test(A,B)VALUES('B','B','2001-01-02')
> SELECT * FROM #Test
> /* A COUNT COUNT/TOTAL Somedate
> A 2 0.40 2001-01-01
> A 1 0.20 2001-01-01
> A 2 0.40 2001-01-01
> B 1 0.50 2001-01-01
> B 1 0.50 2001-01-01
> B 1 0.50 2001-01-02
> B 1 0.50 2001-01-02
> */
> DROP TABLE #Test
>|||Fix your table to have a key so that there are no duplicates. That will save
you from dealing with complex formulations for simple queries in the first
place.
Anith

Don't use dateadd

I've got a reasonably efficient query that gives me a count of the top 20 daily values in my database. Now I'd like to figure out the daily total of top 20 values. Then analyze this information to work out the average, min, max and standard deviation of the daily total of top 20 values.

My best effort is horribly slow - does anyone have a better idea how to do this?

Thanks!

The schema of the database it accesses:

create view eventView (timeStr, msec, host, process, dbName, point,
description, rtu, groupName, message, type,
sevInt, time)
as
select dateadd(second,time+60*offset,'01/01/70'), msec, host, process,
dbName, ptName, description, rtuName, groupName,
message, type, sevInt, time
from summary

CREATE TABLE [summary] (
[msrepl_tran_version] uniqueidentifier ROWGUIDCOL NOT NULL CONSTRAINT [DF_Summary_GUID] DEFAULT (newid()),
[time] [GMTtime] NOT NULL ,
[msec] [int] NULL ,
[offset] [GMToffset] NOT NULL ,
[type] [nameType] NULL ,
[host] [nameType] NULL ,
[process] [nameType] NULL ,
[dbName] [nameType] NULL ,
[ptName] [nameType] NULL ,
[description] [descType] NULL ,
[rtuName] [nameType] NULL ,
[groupName] [nameType] NULL ,
[message] [msgType] NOT NULL ,
[fgInt] [tinyint] NULL ,
[sevInt] [tinyint] NULL ,
[key1] [int] NULL ,
[key2] [int] NULL ,
[spooler] [tinyint] NULL
) ON [PRIMARY]
GO

My Top 20 query:

SELECT TOP 20 count (*) as "Number of Alarms", [point], [description]

FROM [event].[dbo].[eventView]
WHERE ([timestr] < left(getdate(),11) and [timestr] >= left(getdate() - 1,11))
GROUP BY point, description HAVING count(*) > 1
ORDER BY "Number of Alarms" desc

And the messy, slow meta query:

declare @.myDay datetime
declare @.begDay datetime

declare @.myTable
table(Alarms int, Point varchar(250), Description varchar(250), Before datetime, After datetime)

declare @.myDaily
table(Date datetime, Alarms int)

select @.myDay = left(getdate(),11)

select @.begDay = left (convert(datetime, '12/01/2006'), 11)

while @.begDay <= @.myDay
begin

insert into @.myTable
SELECT TOP 20 count (*) as "Number of Alarms", [point], [description],@.begDay
FROM [event].[dbo].[eventView]
where ([timestr] < @.begDay and [timestr] >= dateadd(day,-1,@.begDay))
group by point, description
having count(*) > 1
order by "Number of Alarms" desc

select @.begDay = dateadd(day,1,@.begDay)

end

--

insert into @.myDaily
select After as "Date", sum(Alarms) as "Alarms"
from @.myTable
group by After
--

select count(Alarms) as "Count", avg(Alarms) as "Average", max(Alarms) as "Maximum", min(Alarms) as "Minimum", stdev (Alarms) as "Standard Deviation"
from @.myDaily

As I know you can improve performance if you make some change in these places.
1. avoid using function in your where clause.
2. create index in summary table.
3. I am not sure why you have to use dateadd(second,time+60*offset,'01/01/70'), in eventView? You can create another column which store time as your local timezone.


|||

I'm still learning SQL, so I don't understand all of your suggestions:

1. Avoid using function in where clause

Are you referring to the dateadd? How else can I limit the data to daily information?

2. create index in summary table.

Sorry, can't do that. I don't have control over the summary table - it's provided to my company by the owner of the software.

3. why you have to use dateadd(second,time+60*offset,'01/01/70'), in eventView

Because the software mfg stores data in the summary table in the format of "UTC seconds ". Once again, I cannot change the summary table.

|||If I were you, I won't use dateadd(....) in eventView. That is we still use UTC timestamp in eventView.

you can create @.begDay_UTC and @.PreviousDay_UTC and use them in this part of code in your where clause.
([timestr] < @.begDay_UTC and [timestr] >= @.PreviousDay_UTC)

If you can find out index Summary table used, that will help us find out how to improve performance.

Also, the estimated executions in SQL Server Management Studio will help us find out which part of code is the most expensive.|||

You can replace that while loop with a single query that will greatly improve the performance, here it is (I think, haven't tested but should be very close)

insert into @.myTable
SELECT TOP 20 count (*) as "Number of Alarms", [point], [description], [timestr], dateadd(day, 1, [timestr])
FROM [event].[dbo].[eventView]
where [message] not like '%NORMAL state%' and
[message] not like '%restored - normal%' and
[message] not like '%communication%restored%' and
[message] not like '%PLM - NORMAL%' and
[type] = 'alarm' and
[timestr] between @.begDay and @.myDay
group by [timestr], dateadd(day, 1, [timestr]), point, description, dbName
having count(*) > 1
order by "Number of Alarms" desc

|||

Agree this makes more sense and is easier to read. I've tested this and it does not make much difference in the performance.

I'm going to use your suggestion - much cleaner and easier to understand! Thanks...

|||

Think you are trying to grab the daily information using the GROUP BY.

This doesn't work for me because I'm trying to get a set of n days ( = 265 days on my system) daily top 20 values; this query only returns 20 values. I want 265 x 20 values.

|||

Which version of SQL Server are you using? You can simplify the WHILE loop in SQL Server 2005 using the APPLY operator. In SQL Server 2000, there is no easy way to write a single query - you have to do some sort of procedural loop which might be the fastest way. See below for an example in SQL Server 2005:

-- Top 2 order details based on quantity for each order:

select *
from Orders as o
cross apply (
select top 2 *
from "Order Details" as od
where od.OrderID = o.OrderID
order by od.Quantity
) as o2

|||

Our system runs MS SQL Server 2000. And since its the back end providing data archiving for our turnkey system, we will be using this for years to come.

When you say "procedural loop" that makes me think the BEGIN loop is the only way to do this job. Too bad 8-(

dont permit manual access to database

I've got a java application that connects to a sql server 2000
database.
The application must access with total permissions to database but I
don't want that anybody can insert or delete data with the corporative
administrator of sql server 2000.
How can I lock the corporative administrator in order to not permit
manual manipulation but my application can work properly?
Thanks!There's no way to prevent a sysadmin from accessing a database. You
don't say why you want to do this, but if it's to stop someone seeing
sensitive data, then encryption is probably the best solution:

http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=22

Simon