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?
 
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
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.