SQL Server database Simple SQL statistics

First, build a library table

Use master

if exists (select 1
From sysobjects
WHERE id = object_id (' testsum ')
and type = ' U ')
drop table Testsum
Go

CREATE TABLE Testsum
(
ID int identity (+),
Name nvarchar (") Default ("),
TypeName nvarchar (") Default ("),
num int default (0),
Val float default (9)
)

Begin

declare @i int;
declare @ii int;
Set @i= 1;

while (@i<1000)
Begin
Set @ii = @i%5;
Insert into Testsum (Name,typename,num, Val) values (' Name ' +cast (@i as varchar (4)), ' type ' ++cast (@ii as varchar (1)), CAST (Floo R (Rand () *1000) as int), round ((rand () *1000), 2));
Set @[email protected]+1;
End

End

Second, query the establishment of the table

SELECT * FROM Testsum

Three, enter the topic, simple SQL statement Summary

--1. Total number of data count () functions
Select COUNT (0) from Testsum
where 1=1

--2. Summation sum () function for a field
Select SUM (num), SUM (val) from Testsum;
Select SUM (num) from Testsum
Select SUM (val) from Testsum;

--3. By name category sum, the categorical query must have GROUP by
Select sum (val), name from Testsum
where 1=1
Group BY name
Order BY name

--4. Sum by type classification, quantity, category query must have GROUP by

Select sum (val), Count (TypeName), TypeName from Testsum
where 1=1
GROUP BY TypeName
ORDER BY TypeName

--5 max (), Min min ()
Select Max (val), Min (val), Max (num), min (num) from Testsum

SQL Server database Simple SQL statistics