sql server 2008 query syntax

Hi,

I have a query which tells me the i/o stats for my database for each database file for each data;
However i need to change this to see the total values for each column for that day;
Please help how i can change this column

SELECT CONVERT(VARCHAR(10), n.[Timestamp], 120) as WhatDate,
       n.[physical_name],
       (n.[num_of_bytes_read] - o.[num_of_bytes_read]) as [num_of_bytes_read],
       (n.[num_of_bytes_written] - o.[num_of_bytes_written]) as [num_of_bytes_written],
       (n.[io_stall_write_ms] - o.[io_stall_write_ms]) as [io_stall_write_ms],
       (n.[io_stall] - o.[io_stall]) as [io_stall]
  FROM iostats n
  JOIN iostats o
    ON n.physical_name = o.physical_name
   AND CONVERT(VARCHAR(10), n.[Timestamp], 120) = CONVERT(VARCHAR(10), DATEADD(dd, 1, o.[Timestamp]), 120)


thanks
H
LVL 1
hraja77Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ephraim WangoyaCommented:
try
SELECT CONVERT(VARCHAR(10), n.[Timestamp], 120) as WhatDate,
       n.[physical_name],
       (n.[num_of_bytes_read] - o.[num_of_bytes_read]) as [num_of_bytes_read],
       (n.[num_of_bytes_written] - o.[num_of_bytes_written]) as [num_of_bytes_written],
       (n.[io_stall_write_ms] - o.[io_stall_write_ms]) as [io_stall_write_ms],
       (n.[io_stall] - o.[io_stall]) as [io_stall]       
  FROM iostats n
  JOIN iostats o
    ON n.physical_name = o.physical_name
   AND CONVERT(VARCHAR(10), n.[Timestamp], 120) = CONVERT(VARCHAR(10), DATEADD(dd, 1, o.[Timestamp]), 120)     
union all   
SELECT CONVERT(VARCHAR(10), n.[Timestamp], 120) as WhatDate,
       'Total',
       SUM((n.[num_of_bytes_read] - o.[num_of_bytes_read])),
       SUM((n.[num_of_bytes_written] - o.[num_of_bytes_written])),
       SUM((n.[io_stall_write_ms] - o.[io_stall_write_ms])),
       SUM((n.[io_stall] - o.[io_stall]))
  FROM iostats n
  JOIN iostats o
    ON n.physical_name = o.physical_name
   AND CONVERT(VARCHAR(10), n.[Timestamp], 120) = CONVERT(VARCHAR(10), DATEADD(dd, 1, o.[Timestamp]), 120)

Open in new window

0
hraja77Author Commented:
thanks for that but i get

Msg 8120, Level 16, State 1, Line 12
Column 'iostats.Timestamp' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
0
Ephraim WangoyaCommented:
Thats the toal line, you dont really need it
try
SELECT CONVERT(VARCHAR(10), n.[Timestamp], 120) as WhatDate,
       n.[physical_name],
       (n.[num_of_bytes_read] - o.[num_of_bytes_read]) as [num_of_bytes_read],
       (n.[num_of_bytes_written] - o.[num_of_bytes_written]) as [num_of_bytes_written],
       (n.[io_stall_write_ms] - o.[io_stall_write_ms]) as [io_stall_write_ms],
       (n.[io_stall] - o.[io_stall]) as [io_stall]       
  FROM iostats n
  JOIN iostats o
    ON n.physical_name = o.physical_name
   AND CONVERT(VARCHAR(10), n.[Timestamp], 120) = CONVERT(VARCHAR(10), DATEADD(dd, 1, o.[Timestamp]), 120)     
union all   
SELECT CONVERT(VARCHAR(10), GETDATE(), 120),
       'Total',
       SUM((n.[num_of_bytes_read] - o.[num_of_bytes_read])),
       SUM((n.[num_of_bytes_written] - o.[num_of_bytes_written])),
       SUM((n.[io_stall_write_ms] - o.[io_stall_write_ms])),
       SUM((n.[io_stall] - o.[io_stall]))
  FROM iostats n
  JOIN iostats o
    ON n.physical_name = o.physical_name
   AND CONVERT(VARCHAR(10), n.[Timestamp], 120) = CONVERT(VARCHAR(10), DATEADD(dd, 1, o.[Timestamp]), 120)

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
HainKurtSr. System AnalystCommented:
is your query working fine? can you please post a sample from the query?

you can wrap your originakl query like

select
  physical_name,
  sum(num_of_bytes_read) sum_num_of_bytes_read,
 ...
from (original query here) x
group by physical_name

0
hraja77Author Commented:
thanks but its my fault i am probably misleading you - my base table is:

CREATE TABLE [dbo].[iostats](
 [ID] [int] IDENTITY(1,1) NOT NULL,
 [Timestamp] [datetime] NULL,
 [physical_name] [varchar](500) NOT NULL,
 [num_of_bytes_read] [bigint] NOT NULL,
 [num_of_bytes_written] [bigint] NOT NULL,
 [io_stall_write_ms] [bigint] NOT NULL,
 [io_stall] [bigint] NULL
) ON [PRIMARY]

so if i query of this i need to know for each day the number_of_bytes_read, number_of_bytes written etc;
but the trick is the calues stored in this base table are cumulative - am i making sense - thanks for looking into this
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.