SQL Server Group By Clause

SQL Server Group By
                 --> groups repeated data in a column(s)/Aggregates data


Note: grouping on non-repeated columns such as Primary key/unique key will result entire rows in the table.

Syntax:
           select col1,col2,col3  from table
           group by col1,col2,col3.

syntax 2:
            select col1, max(col2) from table
            group by col1.
       

Here is the Emp table

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
7369	SMITH	CLERK	7902	12/17/1980 12:00:00 AM	800.00		20
7499	ALLEN	SALESMAN	7698	2/20/1981 12:00:00 AM	1600.00	300.00	30
7521	WARD	SALESMAN	7698	2/22/1981 12:00:00 AM	1250.00	500.00	30
7566	JONES	MANAGER	7839	4/2/1981 12:00:00 AM	2975.00		20
7654	MARTIN	SALESMAN	7698	9/28/1981 12:00:00 AM	1250.00	1400.00	30
7698	BLAKE	MANAGER	7839	5/1/1981 12:00:00 AM	2850.00		30
7782	CLARK	MANAGER	7839	6/9/1981 12:00:00 AM	2450.00		10
7839	KING	PRESIDENT		11/17/1981 12:00:00 AM	5000.00		10
7844	TURNER	SALESMAN	7698	9/8/1981 12:00:00 AM	1500.00	0.00	30
7900	JAMES	CLERK	7698	12/3/1981 12:00:00 AM	950.00		30
7902	FORD	ANALYST	7566	12/3/1981 12:00:00 AM	3000.00		20
7934	MILLER	CLERK	7782	1/23/1982 12:00:00 AM	1300.00		10

Note:In this table deptno and job columns have repeated values. Which are meaningful in-context.

Grouping Data on Single Column(DeptNo)

--Get Employee Count for each department

select deptno, count(*) Employees from emp
group by Deptno

deptno    Employees
10    3
20    3
30    6


--Get Employee max salary for each department

select deptno, Max(sal) "Max Salary" from emp
group by Deptno

deptno    Max Salary
10    5000.00
20    3000.00
30    2850.00

Grouping Data on Multi-Column(DeptNo,JOB)

--Get Employee count from each department based on job titleselect deptno, job,count(*) Employees from emp
group by Deptno,job
 
deptno    job    Employees
20    ANALYST    1
10    CLERK    1
20    CLERK    1
30    CLERK    1
10    MANAGER    1
20    MANAGER    1
30    MANAGER    1
10    PRESIDENT    1
30    SALESMAN    4

--Get Employee Max Salary in each department and job.
select deptno, job,Max(sal) "Max Salary" from emp
group by Deptno,job
 
deptno    job    Employees
20    ANALYST    3000.00
10    CLERK    1300.00
20    CLERK    800.00
30    CLERK    950.00
10    MANAGER    2450.00
20    MANAGER    2975.00
30    MANAGER    2850.00
10    PRESIDENT    5000.00
30    SALESMAN    1600.00
 

 Note: All columns specified in the select list(except those in aggregate functions)  should be part of group by clause.

otherwise Msg 8120, Level 16, State 1, Line 18
Column 'emp.[]' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.


Tags: SQL Server Group By Clause, Group by Examples, Group by on single column, Group By on Multiple Column

Connect to SQL Server 2012 using PowerShell

Connect to SQL Server 2012 using PowerShell

Step by Step Process to query a table using SQL Server PowerShell

1)  Open SQLPS.exe located in

C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn

                    it will display PS SQLSERVER:\>

2)  Now goto SQL 

   by typing    PS SQLSERVER:\>set-location SQL   enter

               it will become

                PS SQLSERVER:\SQL>

3) Get Machine Name:

       
PS SQLSERVER:\SQL>dir

       Machine-Name

4)    Now goto MachineName (got in step 3)

          
PS SQLSERVER:\SQL>set-location Machine-Name

5)  Now get list of sql server instances  running in that machine

PS SQLSERVER:\SQL\machine-name>dir

        

Instance Name
-------------
SQLEXPRESS2012

6)  now get list of databases 

connect to SQL Server Instance
PS SQLSERVER:\SQL\machine-name>set-location SQLEXPRESS2012

Step 7) Get List of Objects in SQL-Server Instance

PS SQLSERVER:\SQL\machine-name\SQLEXPRESS2012>dir

Audits
AvailabilityGroups
BackupDevices
Credentials
CryptographicProviders
Databases
Endpoints
JobServer
Languages
LinkedServers
Logins
Mail
ResourceGovernor
Roles
ServerAuditSpecifications
SystemDataTypes
SystemMessages
Triggers
UserDefinedMessages

Step 8) Get List of Databases;

PS SQLSERVER:\SQL\machine-name\SQLEXPRESS2012>set-location databases;

PS SQLSERVER:\SQL\machine-name\SQLEXPRESS2012\databases>dir

Name                 Status          Containment Type Recovery Model CompatLvl Collation                      Owner
----                 ------          ---------------- -------------- --------- ---------                      -----
AdventureWorks   Normal, AutoClo None             Simple               110 SQL_Latin1_General_CP1_CI_AS   sa
                     sed                                                                                      i

BooksList            Normal, AutoClo None             Simple               110 Latin1_General_CI_AI           sa
                                                                                                                 i
EntLibQuickStarts    Normal, AutoClo None             Simple               110 SQL_Latin1_General_CP1_CI_AS   sa
                     sed
Northwind            Normal, AutoClo None             Simple               110 Latin1_General_CI_AI           sa

Step 9)  Connect to Specific database;

PS SQLSERVER:\SQL\machine-name\SQLEXPRESS2012\databases>set-location Adventureworks


PS SQLSERVER:\SQL\machine-name\SQLEXPRESS2012\databases\Adventureworks>

step 10)   Get Connected DB Name

PS SQLSERVER:\SQL\machine-name\SQLEXPRESS2012\databases\Adventureworks> invoke-sqlcmd -query "select db_name()"
 
Column1
-------
AdventureWorks


Step 11) Issue a select statement to a Table/view

PS SQLSERVER:\sql\machine-name\sqlexpress2012\databases\AdventureWorks> invoke-sqlcmd -query "select count(*) from sales.store"
WARNING: Using provider context. Server = MACHINE-NAME\SQLEXPRESS2012, Database = AdventureWorks2012.

                                                                                                                                Column1
                                                                                                                                -------
                                                                                                                                    701


Note:  you can do all database operations using SQL SERVER POWERSHELL



Step 12)   One Step Process to connect to Database

If you already know your machine name,sql server instance and database name. Here is the Procedure

PS SQLSERVER:\>set-location sql\Machine-Name\SQL Instance Name\databases\adventureworks

PS SQLSERVER:\sql\machine-name\sqlexpress\databases\adventureworks>

Then issue SQL command here


PS SQLSERVER:\sql\machine-name\sqlexpress\databases\adventureworks>invoke-sqlcmd -query "select count(*) from sales.store"

Output:
WARNING: Using provider context. Server = machine-name\SQLEXPRESS2012, Database = AdventureWorks2012.

                                                                                                                                Column1
                                                                                                                                -------
                                                                                                                                    701


Happy coding ....

Tags:Step by Step process to connect to Database using PowerShell in SQLSERVER,Set-Location in PowerShell,Invoke-sqlcmd in Powershell,SQL Server Powershell command Invoke-sqlcmd,
SQL Server Powershell command Set-Location,Query Database using SQL SERVER PowerShell,
Query Table using SQL-Server PowerShell,using SQL Server Power Shell.

Find SQL Server Version

Find SQL Server Version

exec sys.sp_server_info;

SQL Server Server information

attribute_name	attribute_value
DBMS_NAME	Microsoft SQL Server
DBMS_VER	Microsoft SQL Server 2012 - 11.0.2100.60 - 11.0.2100.60
OWNER_TERM	owner
TABLE_TERM	table
MAX_OWNER_NAME_LENGTH	128
TABLE_LENGTH	128
MAX_QUAL_LENGTH	128
COLUMN_LENGTH	128
IDENTIFIER_CASE	MIXED
TX_ISOLATION	2
COLLATION_SEQ	charset=iso_1 collation=Latin1_General_CI_AI
SAVEPOINT_SUPPORT	Y
MULTI_RESULT_SETS	Y
ACCESSIBLE_TABLES	Y
USERID_LENGTH	128
QUALIFIER_TERM	database
NAMED_TRANSACTIONS	Y
SPROC_AS_LANGUAGE	Y
ACCESSIBLE_SPROC	Y
MAX_INDEX_COLS	16
RENAME_TABLE	Y
RENAME_COLUMN	Y
DROP_COLUMN	Y
INCREASE_COLUMN_LENGTH	Y
DDL_IN_TRANSACTION	Y
DESCENDING_INDEXES	Y
SP_RENAME	Y
REMOTE_SPROC	Y
500 SYS_SPROC_VERSION	11.00.2100

Tags:SQL Server version,Find SQL Server Version,Find SQL Server Current Database,Find SQL Server Current User,Find SQL Server Server Version,Find SQL Server Protocol Version,Find SQL Server Connection,Find SQL Server Server characterset,Find SQL Server DB characterset,Find SQL Server client Characterset,find SQL Server Client Character Set,Find SQL Server port,Find SQL Server Uptime.

SQL SERVER DATENAME

SQL SERVER DATENAME

SQL SERVER DATEPART

SQL SERVER DATEPART 

  DATEPART function used to get Portion of the Date/time/datetime/datetimeoffset column/variable.

Portion  can be either year,month ...seconds,milli,microseconds etc.,

datepart	Abbreviations	Usage	Output
year	yy, yyyy	select datepart(yyyy,getdate()), datepart(yy,getdate()), datepart(yyyy,getutcdate()), datepart(yy,getutcdate())	2013,2013,2013,2013
quarter	qq, q	select datepart(qq,getdate()), datepart(q,getdate()), datepart(qq,getutcdate()), datepart(q,getutcdate())	1,1,1,1
month	mm, m	select datepart(month,getdate()), datepart(mm,getdate()), datepart(m,getdate()), datepart(mm,getutcdate()), datepart(m,getutcdate())	2,2,2,2,2
dayofyear	dy, y	select datepart(dayofyear,getdate()), datepart(dy,getdate()), datepart(y,getdate()), datepart(dy,getutcdate()), datepart(y,getutcdate())	50,50,50,49,49
day	dd, d	select datepart(day,getdate()), datepart(dd,getdate()), datepart(d,getdate()), datepart(dd,getutcdate()), datepart(d,getutcdate())	19,19,19,18,18
week	wk, ww	select datepart(week,getdate()), datepart(wk,getdate()), datepart(ww,getdate()), datepart(wk,getutcdate()), datepart(ww,getutcdate())	8,8,8,8,8
weekday	dw	select datepart(weekday,getdate()), datepart(dw,getdate()), datepart(weekday,getutcdate()), datepart(dw,getutcdate())	3,3,2,2
hour	hh	select datepart(hour,getdate()), datepart(hh,getdate()), datepart(hour,getutcdate()), datepart(hh,getutcdate())	1,1,20,20
minute	mi, n	select datepart(minute,getdate()), datepart(mi,getdate()), datepart(n,getdate()), datepart(mi,getutcdate()), datepart(n,getutcdate())	41,41,41,11,11
second	ss, s	select datepart(second,getdate()), datepart(ss,getdate()), datepart(s,getdate()), datepart(ss,getutcdate()), datepart(s,getutcdate())	34,34,34,34,34
millisecond	ms	select datepart(millisecond,getdate()), datepart(ms,getdate()), datepart(millisecond,getutcdate()), datepart(ms,getutcdate())	630,630,627,627
microsecond	mcs	select datepart(microsecond,getdate()), datepart(mcs,getdate()), datepart(microsecond,getutcdate()), datepart(mcs,getutcdate())	747000,747000,747000,747000
nanosecond	ns	select datepart(nanosecond,getdate()), datepart(ns,getdate()), datepart(nanosecond,getutcdate()), datepart(ns,getutcdate())	640000000,640000000,637000000,637000000
TZoffset	tz	select datepart(TZoffset,getdate()), datepart(tz,getdate()), datepart(TZoffset,getutcdate()), datepart(tz,getutcdate())	Msg 9810, Level 16, State 6, Line 1 The datepart tzoffset is not supported by date function datepart for data type unknown.
ISO_WEEK	isowk, isoww	select datepart(ISO_WEEK,getdate()), datepart(isowk,getdate()), datepart(isoww,getdate()), datepart(isowk,getutcdate()), datepart(isoww,getutcdate())	8,8,8,8,8

datepart	Abbreviations	Usage	Output
year	yy, yyyy	select datepart(yyyy,getdate()), datepart(yy,getdate()), datepart(yyyy,getutcdate()), datepart(yy,getutcdate())	2013,2013,2013,2013
quarter	qq, q	select datepart(qq,getdate()), datepart(q,getdate()), datepart(qq,getutcdate()), datepart(q,getutcdate())	1,1,1,1
month	mm, m	select datepart(month,getdate()), datepart(mm,getdate()), datepart(m,getdate()), datepart(mm,getutcdate()), datepart(m,getutcdate())	2,2,2,2,2
dayofyear	dy, y	select datepart(dayofyear,getdate()), datepart(dy,getdate()), datepart(y,getdate()), datepart(dy,getutcdate()), datepart(y,getutcdate())	50,50,50,49,49
day	dd, d	select datepart(day,getdate()), datepart(dd,getdate()), datepart(d,getdate()), datepart(dd,getutcdate()), datepart(d,getutcdate())	19,19,19,18,18
week	wk, ww	select datepart(week,getdate()), datepart(wk,getdate()), datepart(ww,getdate()), datepart(wk,getutcdate()), datepart(ww,getutcdate())	8,8,8,8,8
weekday	dw	select datepart(weekday,getdate()), datepart(dw,getdate()), datepart(weekday,getutcdate()), datepart(dw,getutcdate())	3,3,2,2
hour	hh	select datepart(hour,getdate()), datepart(hh,getdate()), datepart(hour,getutcdate()), datepart(hh,getutcdate())	1,1,20,20
minute	mi, n	select datepart(minute,getdate()), datepart(mi,getdate()), datepart(n,getdate()), datepart(mi,getutcdate()), datepart(n,getutcdate())	41,41,41,11,11
second	ss, s	select datepart(second,getdate()), datepart(ss,getdate()), datepart(s,getdate()), datepart(ss,getutcdate()), datepart(s,getutcdate())	34,34,34,34,34
millisecond	ms	select datepart(millisecond,getdate()), datepart(ms,getdate()), datepart(millisecond,getutcdate()), datepart(ms,getutcdate())	630,630,627,627
microsecond	mcs	select datepart(microsecond,getdate()), datepart(mcs,getdate()), datepart(microsecond,getutcdate()), datepart(mcs,getutcdate())	747000,747000,747000,747000
nanosecond	ns	select datepart(nanosecond,getdate()), datepart(ns,getdate()), datepart(nanosecond,getutcdate()), datepart(ns,getutcdate())	640000000,640000000,637000000,637000000
TZoffset	tz	select datepart(TZoffset,getdate()), datepart(tz,getdate()), datepart(TZoffset,getutcdate()), datepart(tz,getutcdate())	Msg 9810, Level 16, State 6, Line 1 The datepart tzoffset is not supported by date function datepart for data type unknown.
ISO_WEEK	isowk, isoww	select datepart(ISO_WEEK,getdate()), datepart(isowk,getdate()), datepart(isoww,getdate()), datepart(isowk,getutcdate()), datepart(isoww,getutcdate())	8,8,8,8,8

Ex:Get Time only  from datetime column/GetDate()

select concat(datepart(hh,getdate()),':',datepart(minute,getdate()),':',datepart(ss,getdate()))
O/P:
time
1:58:46

Tags:SQL SERVER DATEPART ,DatePart year,
DatePart month,DatePart dayofweek,
DatePart dayofyear,DatePart day,DatePart hour,DatePart minute,
DatePart second,
DatePart microsecond,DatePart millisecond,
DatePart  datetime offset,
DatePart iso week,
DatePart quarter

SQL SERVER JOINS

SQL SERVER JOINS

JOINS are used to combine 2 or more tables which has common columns.

  SQL SERVER  has following Joins

•  INNER JOINS
• OUTER JOINS
  • FULL
  • LEFT
  • RIGHT
• SELF JOIN
• CROSS JOIN 

TAGS:

SQL SERVER JOINS,SQL SERVER INNER JOIN,SQL SERVER FULL OUTER JOIN,SQL SERVER LEFT OUTER JOIN,SQL SERVER RIGHT OUT JOIN,SQL SERVER SELF JOIN,SQL SERVER CROSS JOIN,SQL SERVER CARTESIAN PRODUCT JOIN.

SQL SERVER QUOTENAME String Function

SQL SERVER QUOTENAME String Function

QUOTENAME String function adds a delimiter to a String .

EX: Add SQL-SERVER DEFAULT DEMILITER FOR QUOTEs I.E '['

select 'swamy vivekanda says:'+quotename('strenth is life,weekness is death')

o/p:

swamy vivekanda says:[strenth is life,weekness is death]

Ex: QUOTENAME delimiter "

select 'swamy vivekanda says:'+quotename('strenth is life,weekness is death','''') quotename

O/P:
quotename
swamy vivekanda says:'strenth is life,weekness is death'

select 'swamy vivekanda says:'+quotename('strenth is life,weekness is death','"') quotename

O/P:
quotename
swamy vivekanda says:"strenth is life,weekness is death"


Tags:SQL SERVER QUOTENAME String Function,QUOTENAME DELIMITER,QUOTENAME DEMILITER ' ,SQL SERVer QUOTENAME NAME ESCAPE CHARACTERS

SQL SERVER REPLICATE STRING FUNCTION

SQL SERVER REPLICATE STRING FUNCTION

Replicate String function - Repeats a string value n number of times

Replicate(StringToRepeat,nTimes)

Ex: Repeat 100 5 times in T-SQL

select replicate(100,5) [100*5]

O/P:

100*5
100100100100100

Ex:Display Integers in 3 digit format using REPLICATE function

declare @i smallint,@j smallint
set @i=1
set @j=2
select concat(replicate('0',3-len(@i)),@i) i,concat(replicate('0',3-len(@j)),@j) j

O/P:

i    j
001    002

Note: Same thing can be applied to any table column.

Ex:Convert String to formatted Data  using REPLICATE(LEFT SIDE)

declare @custCode varchar(5)
set @custCode='123'
select concat(REPLICATE('#',5-datalength(@custCode)),@custCode) customerCode

O/P:

customerCode
##123

Ex:Convert String to formatted Data  using REPLICATE(RIGHT SIDE)

declare @custCode varchar(5)
set @custCode='123'
select concat(@custCode,REPLICATE('#',5-datalength(@custCode))) customerCode

O/P:

customerCode
123##

Tags:SQL SERVER REPLICATE STRING FUNCTION,REPEAT NUMBER IN SQL SERVER,REPLICATE IN SQL SERVER,STRING CONCAT IN SQL-SERVER,DISPLAY INTEGERS IN FORMATTED OUTPUT,DISPLAY CUSTOMEr CODE IN FORMATTED OUTPUT.

SQL SERVER DATATYPES and .NET Framework Datatypes mapping

SQL SERVER DATATYPES and .NET Framework Datatypes mapping

datetime

SQL SERVER DATA TYPES	.NET CLR DATATYPES
bigint	int64
int	int32
smallint	int16
tinyint	byte
decimal	SqlDecimal
numeric	SqlDecimal
float	double
real	short
smallmoney	decimal
money	decimal
date	datetime
datetime	datetime
time	timespan
smalldatetime	datetime
datetime2	datetime
datetimeoffset	datetimeoffset

tags: SQL DATA TYPES mapping in .NET Data types,SQL Data types  equivalent in C#,SQL Data types  equivalent in Vb.net, SQL Data types and .net data types mapping.

SQL SERVER TOP CLAUSE

SQL SERVER TOP CLAUSE   returns rows by number, percentage.

for ex:  select top 3 highest paid employees in emp table

select  top 3 * from emp;

EMPNO    ENAME    JOB    MGR    HIREDATE    SAL    COMM    DEPTNO
7839    KING    PRESIDENT    NULL    1981-11-17    5000.00    NULL    10
7902    FORD    ANALYST    7566    1981-12-03    3000.00    NULL    20
7566    JONES    MANAGER    7839    1981-04-02    2975.00    300.00    20


EMP TABLE

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
7369	SMITH	CLERK	7902	12/17/1980 12:00:00 AM	800.00		20
7499	ALLEN	SALESMAN	7698	2/20/1981 12:00:00 AM	1600.00	300.00	30
7521	WARD	SALESMAN	7698	2/22/1981 12:00:00 AM	1250.00	500.00	30
7566	JONES	MANAGER	7839	4/2/1981 12:00:00 AM	2975.00		20
7654	MARTIN	SALESMAN	7698	9/28/1981 12:00:00 AM	1250.00	1400.00	30
7698	BLAKE	MANAGER	7839	5/1/1981 12:00:00 AM	2850.00		30
7782	CLARK	MANAGER	7839	6/9/1981 12:00:00 AM	2450.00		10
7839	KING	PRESIDENT		11/17/1981 12:00:00 AM	5000.00		10
7844	TURNER	SALESMAN	7698	9/8/1981 12:00:00 AM	1500.00	0.00	30
7900	JAMES	CLERK	7698	12/3/1981 12:00:00 AM	950.00		30
7902	FORD	ANALYST	7566	12/3/1981 12:00:00 AM	3000.00		20
7934	MILLER	CLERK	7782	1/23/1982 12:00:00 AM	1300.00		10

Ex:  Get top 5 Maximum sold products (from northwind)

select top 5 p.productid,od.productid,ProductName,od.[Total Price] from products p
join
(
select productid,Quantity*unitprice [Total Price] from [order details]
) od
on p.productid=od.productid
order by [Total Price] desc

O/P:
productid    productid    ProductName    Total Price
38    38    Côte de Blaye    15810.00
38    38    Côte de Blaye    15810.00
38    38    Côte de Blaye    10540.00
38    38    Côte de Blaye    10540.00
38    38    Côte de Blaye    10540.00


Ex:get top 3 shipped countries

select top 3 shipcountry,count(*) shipped from orders group by shipcountry
order by shipped desc
                             OR
select top 3  *
from (select shipcountry,count(*) shipped from orders group by shipcountry) o
order by shipped desc

shipcountry    shipped
USA    122
Germany    122
Brazil    83


Ex: get 5 percentage of orders placed in year 1996(northwind DB)

select top 5 percent  * from orders
where DATEPART(year,ShippedDate)=1996

User will get 8 rows, out of 143 rows   5%of 143 = 7.15 it will be ceiled ,so you will get 8 records

Tags:SQL SERVER TOP CLAUSE,using TOP CLUASE in T-SQL, using TOP number,using TOP percent in SQL-SERVER

SQL SERVER ROW_NUMBER

SQL Server ROW_NUMBER returns sequential number of a row with in a partition list.

Row_Number must be used with OVER clause in SQL SERVER other wise
User will get error message "The function 'row_number' must have an OVER clause."

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
7369	SMITH	CLERK	7902	12/17/1980 12:00:00 AM	800.00		20
7499	ALLEN	SALESMAN	7698	2/20/1981 12:00:00 AM	1600.00	300.00	30
7521	WARD	SALESMAN	7698	2/22/1981 12:00:00 AM	1250.00	500.00	30
7566	JONES	MANAGER	7839	4/2/1981 12:00:00 AM	2975.00		20
7654	MARTIN	SALESMAN	7698	9/28/1981 12:00:00 AM	1250.00	1400.00	30
7698	BLAKE	MANAGER	7839	5/1/1981 12:00:00 AM	2850.00		30
7782	CLARK	MANAGER	7839	6/9/1981 12:00:00 AM	2450.00		10
7839	KING	PRESIDENT		11/17/1981 12:00:00 AM	5000.00		10
7844	TURNER	SALESMAN	7698	9/8/1981 12:00:00 AM	1500.00	0.00	30
7900	JAMES	CLERK	7698	12/3/1981 12:00:00 AM	950.00		30
7902	FORD	ANALYST	7566	12/3/1981 12:00:00 AM	3000.00		20
7934	MILLER	CLERK	7782	1/23/1982 12:00:00 AM	1300.00		10

Ex: Display salary in descending order for each department  using row_number

select row_number() over(partition by deptno order by sal desc) rownumber,e.* from emp e

O/P:
rownumber    EMPNO    ENAME    JOB    MGR    HIREDATE    SAL    COMM    DEPTNO
1    7839    KING    PRESIDENT    NULL    1981-11-17    5000.00    NULL    10
2    7782    CLARK    MANAGER    7839    1981-06-09    2450.00    NULL    10
3    7934    MILLER    CLERK    7782    1982-01-23    1300.00    NULL    10
1    7902    FORD    ANALYST    7566    1981-12-03    3000.00    NULL    20
2    7566    JONES    MANAGER    7839    1981-04-02    2975.00    300.00    20
3    7369    SMITH    CLERK    7902    1980-12-17    800.00    NULL    20
1    7698    BLAKE    MANAGER    7839    1981-05-01    2850.00    NULL    30
2    7499    ALLEN    SALESMAN    7698    1981-02-20    1600.00    300.00    30
3    7844    TURNER    SALESMAN    7698    1981-09-08    1500.00    0.00    30
4    7521    WARD    SALESMAN    7698    1981-02-22    1250.00    500.00    30
5    7654    MARTIN    SALESMAN    7698    1981-09-28    1250.00    1400.00    30
6    7900    JAMES    CLERK    7698    1981-12-03    950.00    NULL    30

Ex:get top 3 salaries from emp table using ROW_NUMBER

select * from  (select ROW_NUMBER() over(order by sal desc) rowindex,ename,sal from emp e) ee where rowindex <=3

O/P:
rowindex    ename    sal
1    KING    5000.00
2    FORD    3000.00
3    JONES    2975.00

Ex: get 5th salary from emp table using ROW_NUMBER

select * from  (select ROW_NUMBER() over(order by sal desc) rowindex,ename,sal from emp e) ee where rowindex =5

O/P:
rowindex    ename    sal
5    CLARK    2450.00

Tags:SQL SERVER ROW_NUMBER,SQL SERVER ROWN_NUMBER inline query,ROW_NUMBER with OVER Clause,ROW_NUMBER ORDER By CLAUSE,ROW_NUMBER PARTITION

SQL SERVER GROUP BY clause

SQL SERVER GROUP BY clause

GROUP BY clause  is used to group repeated data like dept number, year,job title,customer id, etc.,

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
7369	SMITH	CLERK	7902	12/17/1980 12:00:00 AM	800.00		20
7499	ALLEN	SALESMAN	7698	2/20/1981 12:00:00 AM	1600.00	300.00	30
7521	WARD	SALESMAN	7698	2/22/1981 12:00:00 AM	1250.00	500.00	30
7566	JONES	MANAGER	7839	4/2/1981 12:00:00 AM	2975.00		20
7654	MARTIN	SALESMAN	7698	9/28/1981 12:00:00 AM	1250.00	1400.00	30
7698	BLAKE	MANAGER	7839	5/1/1981 12:00:00 AM	2850.00		30
7782	CLARK	MANAGER	7839	6/9/1981 12:00:00 AM	2450.00		10
7839	KING	PRESIDENT		11/17/1981 12:00:00 AM	5000.00		10
7844	TURNER	SALESMAN	7698	9/8/1981 12:00:00 AM	1500.00	0.00	30
7900	JAMES	CLERK	7698	12/3/1981 12:00:00 AM	950.00		30
7902	FORD	ANALYST	7566	12/3/1981 12:00:00 AM	3000.00		20
7934	MILLER	CLERK	7782	1/23/1982 12:00:00 AM	1300.00		10

 

Ex:  get total  number of employees from each department 


select deptno,count(*) total from emp group by deptno

deptno    total
10    3
20    3
30    6

 

Ex: get number of employees in each job title.

 

select job,count(*) total from emp group by job

 

job    total
ANALYST    1
CLERK    3
MANAGER    3
PRESIDENT    1
SALESMAN    4

 

Ex: get average salary by job category

select job,avg(sal) 'avg salary' from emp group by job

job    avg salary
ANALYST    3000.000000
CLERK    1016.666666
MANAGER    2758.333333
PRESIDENT    5000.000000
SALESMAN    1400.000000

Ex: get order count for each year and month.

used Northwind Orders Table.

select Datepart(year,orderdate) Year,
Datepart(month,orderdate) month,
count(*) total from orders
group by Datepart(year,orderdate),Datepart(month,orderdate)
order by Datepart(year,orderdate),Datepart(month,orderdate)

1996    7    22
1996    8    25
1996    9    23
1996    10    26
1996    11    25
1996    12    31
1997    1    33
1997    2    29
1997    3    30
1997    4    31
1997    5    32
1997    6    30
1997    7    33
1997    8    33
1997    9    37
1997    10    38
1997    11    34
1997    12    48
1998    1    55
1998    2    54
1998    3    73
1998    4    74
1998    5    14

Tags:SQL SERVER GROUP BY clause ,SQL SERVEr DATE FUNCTION Datepart,SQL Server Aggregate function count

SQL SERVER STR String function

SQL SERVER STR String function

STR  String function  converts from numeric data to character data.


Ex: Convert numeric value to String using STR function

select str(10.55)

O/P:  11
if u don't specify optional params in STR function, it will be rounded to nearest integer.


Ex: Convert  Money to String using STR String function
declare @sal smallmoney
set @sal = 10000.4555
select str(@sal,8,2)

O/P:10000.46

Note: before decimal it has 5 digits after decimal 4 digits so we need 5 digits+2 digits with decimal digit=8, decimal digits 2.



tags:SQL SERVER STR String function,Convert numeric value to String using STR function,Convert  Money to String using STR String function

SQL SERVER SPACE String function

SQL SERVER SPACE String function

 Space String function adds space between Strings.

Ex: Concat unicode Strings using SPACE String function

select concat(N'Ich',space(1),'komme',space(1),'aus',space(1),'Deutschland') german

O/P:

german
Ich komme aus Deutschland

 Ex:Concat strings using Space String function

select concat(N'I',space(1),'am',space(1),'from',space(1),'germany') english

O/P:  

english

I am from germany

Ex: Replace telephone numbers separator -  with  spaces

declare @telehpne char(12)
set @telehpne='409-345-4356'
set @telehpne=replace(@telehpne,'-',space(1))
select @telehpne

O/P:  409 345 4356

Tags: SQL Server Concat String function,SQL Server Space String function,T-SQL concat,Concat 2 numeric literals in SQL Server,Concat Unicode chars in SQL Server,Concat Ascii chars in SQL Server

SQL SERVER CONCAT string function

SQL SERVER CONCAT string function

CONCAT String function concatenates 2 or more Strings into Single String

  If type is not string it will be converted to String type before concatinating.

Ex:Display Full Name with title from Employees table.

select concat(TitleofCourtesy,FirstName,' ',LastName) from Employees

Full Name with title
Ms.Nancy Davolio
Dr.Andrew Fuller
Ms.Janet Leverling
Mrs.Margaret Peacock
.

.

.

Ex:Concat 2 numbers

select concat(123,433)

O/P: 123433

Ex:Contact 2 numeric Expressions 

select concat(123-455,433-30)

O/P: -332403

 first 123-455  = -332

 second 433-30=403

contact(-332,403) gives above value.

Ex: Concat 2 integer expressions

select concat(2012-03-22,2013-03-22)

O/P: 19871988

first 2012-03  gives 2009  2009-22=1987

 same thing for second expression. Because all types are evaluated before converting to String.

Ex:Concat NameValue Pairs in T-SQL

insert into properties(nv) values (concat('domain name=','www.kicko.com')) 

insert into properties(nv) values (concat('car type'=','suv'))  

Ex:Concat unicode  strings using Space String function in

select concat(N'Ich',space(1),'komme',space(1),'aus',space(1),'Deutschland')

O/P:

german
Ich komme aus Deutschland

 Ex:Concat strings using Space String function

select concat(N'I',space(1),'am',space(1),'from',space(1),'germany') english

O/P: I am from germany

Tags: SQL Server Concat String function,SQL Server Space String function,T-SQL concat,Concat 2 numeric literals in SQL Server,Concat Unicode chars in SQL Server,Concat Ascii chars in SQL Server

SQL SERVER LTRIM,RTRIM STRING FUNCTIONS

SQL SERVER LTRIM,RTRIM STRING FUNCTIONS

LTRIM removes leading spaces from string

RTRIM removes trailing spaces from string

select len(' hello'),ltrim(' hello'),len(ltrim(' hello'))

O/P: 

select len(' hello') length,ltrim(' hello') ltrim,len(ltrim(' hello')) [length after l trim]

length    ltrim    length after l trim
6    hello    5

select ltrim(' hello '),  len(' hello '),len(ltrim(' hello '))

hello     6    5

ltrim removes all leading blanks, len ignors trailing blanks  ,so length will be 6 & 5

EX: Remove all blanks and find length of the String in T-SQL

I/P:   '  hello this is New York, may god bless u '

declare @str varchar(250)
set @str= '  hello this is New York, may god bless u '
select  len(@str) length, ltrim(rtrim(@str)) [after trim],len(ltrim(rtrim(@str))) [after trim length]

O/P:

length    after trim    after trim length
41    hello this is New York, may god bless u    39


Tags:SQL SERVER LTRIM, SQL SERVER RTRIM STRING FUNCTIONS,SQL SERVeR LEN STring function, trim and find length in SQL SERVER

SQL SERVER NCHAR STRING FUNCTION

SQL SERVER UNICODE STRING FUNCTION

SQL SERVER NCHAR STRING FUNCTION

NCHAR String function returns unicode character for a given integer.

select nchar(UNICODE(N'हिन्दी'))

O/P:ह

select UNICODE(N'हिन्दी')),nchar(2361)

O/P:  2361,ह

select nchar(ox0025)  -->   displays %

Select nchar(0x02AE),char(0x02AE)

O/P:(No column name)    (No column name)
ʮ    NULL

Ex: Get each Unicode Characters of nvarchar String  हिन्दी

declare @position int, @txt nvarchar(max);
set @position=1;
set @txt = N'हिन्दी';

while @position <= len(@txt)
begin

declare @t int;
select @t=unicode(substring(@txt,@position,1))
print '&#'+ CONVERT(nvarchar(5),@t)+';'
set @position = @position+1
end

O/P:

&#2361;
&#2367;
&#2344;
&#2381;
&#2342;
&#2368;
 

Note:Copy this unicode characters and put in HTML Page user can get  word Hindi in Hind Lanugage .i.e
हिन्दी

Ex:Get Unicode characters of German String  

"Die komplette Familie ist auf dem Cover der britischen Zeitschrift "Hello!"

declare @position int, @txt nvarchar(max);
set @position=1;
set @txt = N'Die komplette Familie ist auf dem Cover der britischen Zeitschrift "Hello!';

while @position <= Datalength(@txt)
begin

declare @t int;
select @t=unicode(substring(@txt,@position,1))
print '&#'+ CONVERT(nvarchar(5),@t)+';'
set @position = @position+1
end

&#68;
&#105;
&#101;
&#32;
&#107;
&#111;
&#109;
&#112;
&#108;
&#101;
&#116;
&#116;
&#101;
&#32;
&#70;
&#97;
&#109;
&#105;
&#108;
&#105;
&#101;
&#32;
&#105;
&#115;
&#116;
&#32;
&#97;
&#117;
&#102;
&#32;
&#100;
&#101;
&#109;
&#32;
&#67;
&#111;
&#118;
&#101;
&#114;
&#32;
&#100;
&#101;
&#114;
&#32;
&#98;
&#114;
&#105;
&#116;
&#105;
&#115;
&#99;
&#104;
&#101;
&#110;
&#32;
&#90;
&#101;
&#105;
&#116;
&#115;
&#99;
&#104;
&#114;
&#105;
&#102;
&#116;
&#32;
&#34;
&#72;
&#101;
&#108;
&#108;
&#111;
&#33;

Note:Copy this unicode characters and put in HTML Page user can get  word 

"

D i e k o m p l e t t e F a m i l i e i s t a u f d e m C o v e r d e r b r i t i s c h e n Z e i t s c h r i f t " H e l l o !"

 

Tags: SQL SERVER UNICODE STRING FUNCTION,SQL SERVER NCHAR STRING FUNCTION,How to get all unicodes and characters from a String,get all unicodes and unicode characters from a String.

SQL SERVER UNICODE STRING FUNCTION

SQL SERVER UNICODE STRING FUNCTION

UNICODE String function returns UNICODE value of first character in the STRING.

select nchar(UNICODE(N'हिन्दी'))

O/P:ह

select UNICODE(N'हिन्दी'))

O/P:  2361

Ex: Get each Unicode Characters of nvarchar String  हिन्दी

declare @position int, @txt nvarchar(max);
set @position=1;
set @txt = N'हिन्दी';

while @position <= len(@txt)
begin

declare @t int;
select @t=unicode(substring(@txt,@position,1))
print '&#'+ CONVERT(nvarchar(5),@t)+';'
set @position = @position+1
end

O/P:

&#2361;
&#2367;
&#2344;
&#2381;
&#2342;
&#2368;
 

Note:Copy this unicode characters and put in HTML Page user can get  word Hindi in Hind Lanugage .i.e
हिन्दी

Ex:Get Unicode characters of German String  

"Die komplette Familie ist auf dem Cover der britischen Zeitschrift "Hello!"

declare @position int, @txt nvarchar(max);
set @position=1;
set @txt = N'Die komplette Familie ist auf dem Cover der britischen Zeitschrift "Hello!';

while @position <= Datalength(@txt)
begin

declare @t int;
select @t=unicode(substring(@txt,@position,1))
print '&#'+ CONVERT(nvarchar(5),@t)+';'
set @position = @position+1
end

&#68;
&#105;
&#101;
&#32;
&#107;
&#111;
&#109;
&#112;
&#108;
&#101;
&#116;
&#116;
&#101;
&#32;
&#70;
&#97;
&#109;
&#105;
&#108;
&#105;
&#101;
&#32;
&#105;
&#115;
&#116;
&#32;
&#97;
&#117;
&#102;
&#32;
&#100;
&#101;
&#109;
&#32;
&#67;
&#111;
&#118;
&#101;
&#114;
&#32;
&#100;
&#101;
&#114;
&#32;
&#98;
&#114;
&#105;
&#116;
&#105;
&#115;
&#99;
&#104;
&#101;
&#110;
&#32;
&#90;
&#101;
&#105;
&#116;
&#115;
&#99;
&#104;
&#114;
&#105;
&#102;
&#116;
&#32;
&#34;
&#72;
&#101;
&#108;
&#108;
&#111;
&#33;

Note:Copy this unicode characters and put in HTML Page user can get  word 

"

D i e k o m p l e t t e F a m i l i e i s t a u f d e m C o v e r d e r b r i t i s c h e n Z e i t s c h r i f t " H e l l o !"

 

Tags: SQL SERVER UNICODE STRING FUNCTION,SQL SERVER NCHAR STRING FUNCTION,How to get all unicodes and characters from a String,get all unicodes and unicode characters from a String.

SQL SERVER CHAR String FUNCTION

SQL SERVER CHAR String FUNCTION

CHAR function accepts ascii value of the character and returns character value

for ex:   select char(65) ,char(ascii('America')) -->  A  A     65 is ASCII value of capital 'A'.

              select char(97) ,char(ascii('america')) -->  a  a     97 is ASCII value of small 'a'.

Suppose u want to displayCHAR values along with  ASCII values of all characters in the string.

Here is the Procedure.

declare @position int,@ttx nchar(25)
set @position=1;
set @ttx='America!';
set nocount off;
while @position <= LEN(@ttx)
begin
select ascii(substring(@ttx,@position,1)) as ASCII,CHAR(ascii(substring(@ttx,@position,1))) as CHAR;
set @position = @position + 1;
end

O/P:

ASCII    CHAR
65    A

ASCII    CHAR
109    m

ASCII    CHAR
101    e

ASCII    CHAR
114    r

ASCII    CHAR
105    i

ASCII    CHAR
99    c

ASCII    CHAR
97    a

ASCII    CHAR
33    !

tags:SQL Server ASCII String function,SQL Server ASCII function,return ASCII value of String in SQL-Server