CODEWITHSUNDEEP
mysqldatabasesql-server-2008database-design
user1745767
user1745767

Reputation: 1071

Create a summary table with MS SQL 2008

This is how I create a summary table in MySQL, how can I do the same thing in MS SQL 2008?

The idea is to create a summary table that only has dates (not time), and has summed the kWh for each meter per day.

CREATE table summary 
SELECT meterID, date( DateTime ) AS date, sum( kWh ) AS kWh
FROM data GROUP BY meterID, date;"

Upvotes: 1

Views: 1434

Answers (1)

RThomas
RThomas

Reputation: 10882

As for building the table your question doesn't specify exact data types. For example is kWh only an int or could if have a decimal part. But, taking a stab at guessing it would be something like this.

CREATE Table summary
(
 meterID int,
 YourDate date,
 kWh int /* or maybe a decimal(9,2) or something like that */
)

Then the rest is pretty close to mysql, just swapping a Cast in to do the conversion. You could also use convert but I've always liked the syntax of Cast better.

INSERT INTO summary (meterID, YourDate, kWh) 
(SELECT meterID, Cast(YourDateTime AS Date) AS YourDate, sum( kWh ) AS kWh
FROM data GROUP BY meterID, Cast(YourDateTime AS Date));

Upvotes: 2

Related Questions