Group by clause - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Group by clause

Author

Topic

AlanPBates
Starting Member

34 Posts

Posted - 2010-05-25 : 13:43:37

I have a table -

CREATE TABLE [dbo].[PRT_MCP1_Flt](
[Fault] [varchar](20) NULL,
[Occurred] [datetime] NULL,
[Duration] [time](0) NULL,
[MCPID] [int] NULL
) ON [PRIMARY]

Data for table -

insert into MCP1_CommFlt values ('MCP1_CommFlt01', '04-23-2010 18:35:34.447', '2:15', 1)
insert into MCP1_CommFlt values ('MCP1_CommFlt02', '04-23-2010 18:35:34.447', '2:15', 1)
insert into MCP1_CommFlt values ('MCP1_CommFlt03', '04-23-2010 18:35:34.447', '2:15', 1)
insert into MCP1_CommFlt values ('MCP1_CommFlt04', '04-23-2010 18:35:34.447', '2:15', 1)
insert into MCP1_CommFlt values ('MCP1_CommFlt05', '04-23-2010 18:35:34.447', '2:15', 1)

insert into MCP1_EStop values ('MCP1_EStop01', '04-23-2010 18:35:34.447', '1:15',1)
insert into MCP1_EStop values ('MCP1_EStop02', '04-23-2010 18:35:34.447', '2:15',1)
insert into MCP1_EStop values ('MCP1_EStop03', '04-23-2010 18:35:34.447', '3:15',1)
insert into MCP1_EStop values ('MCP1_EStop04', '04-23-2010 18:35:34.447', '4:15',1)
insert into MCP1_EStop values ('MCP1_EStop05', '04-23-2010 18:35:34.447', '5:15',1)

insert into MCP1_MotorFlt values ('MCP1_MotorFlt01', '04-23-2010 18:35:34.447', '3:00',1)
insert into MCP1_MotorFlt values ('MCP1_MotorFlt02', '04-23-2010 18:35:34.447', '3:00',1)
insert into MCP1_MotorFlt values ('MCP1_MotorFlt03', '04-23-2010 18:35:34.447', '3:00',1)
insert into MCP1_MotorFlt values ('MCP1_MotorFlt04', '04-23-2010 18:35:34.447', '3:00',1)
insert into MCP1_MotorFlt values ('MCP1_MotorFlt05', '04-23-2010 18:35:34.447', '3:00',1)

insert into MCP1_PanelFlt values ('MCP1_PanelFlt01', '04-23-2010 18:35:34.447', '1:00',1)
insert into MCP1_PanelFlt values ('MCP1_PanelFlt02', '04-23-2010 18:35:34.447', '2:00',1)
insert into MCP1_PanelFlt values ('MCP1_PanelFlt03', '04-23-2010 18:35:34.447', '3:00',1)
insert into MCP1_PanelFlt values ('MCP1_PanelFlt04', '04-23-2010 18:35:34.447', '4:00',1)
insert into MCP1_PanelFlt values ('MCP1_PanelFlt05', '04-23-2010 18:35:34.447', '5:00',1)

insert into MCP1_PEJam values ('MCP1_PEJam01', '04-23-2010 18:35:34.447', '5:15',1)
insert into MCP1_PEJam values ('MCP1_PEJam02', '04-23-2010 18:35:34.447', '5:15',1)
insert into MCP1_PEJam values ('MCP1_PEJam03', '04-23-2010 18:35:34.447', '5:15',1)
insert into MCP1_PEJam values ('MCP1_PEJam04', '04-23-2010 18:35:34.447', '5:15',1)
insert into MCP1_PEJam values ('MCP1_PEJam05', '04-23-2010 18:35:34.447', '5:15',1)

I need an output somethinglike this -

FAULT Occurances TotalTime

The above data will translate to -

CommFlt 5 11.15
EStop 5 16.15
MotorFlt 5 15.0
PanelFlt 5 15.0
PEJam 5 16.15

The total time is the sum of the faults of the type.
The occurances are number of times the type of fault occur.

Help in SQL will be appreciated ..

Thanks,

Alan.

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2010-05-25 : 14:41:59

IF OBJECT_ID('tempdb..#PRT_MCP1_Flt') IS NOT NULL DROP TABLE #PRT_MCP1_Flt;

CREATE TABLE #PRT_MCP1_Flt(
[Fault] [varchar](20) NULL,
[Occurred] [datetime] NULL,
[Duration] [time](0) NULL,
[MCPID] [int] NULL
) ON [PRIMARY]

insert into #PRT_MCP1_Flt values ('MCP1_CommFlt01', '04-23-2010 18:35:34.447', '2:15', 1)
insert into #PRT_MCP1_Flt values ('MCP1_CommFlt02', '04-23-2010 18:35:34.447', '2:15', 1)
insert into #PRT_MCP1_Flt values ('MCP1_CommFlt03', '04-23-2010 18:35:34.447', '2:15', 1)
insert into #PRT_MCP1_Flt values ('MCP1_CommFlt04', '04-23-2010 18:35:34.447', '2:15', 1)
insert into #PRT_MCP1_Flt values ('MCP1_CommFlt05', '04-23-2010 18:35:34.447', '2:15', 1)

insert into #PRT_MCP1_Flt values ('MCP1_EStop01', '04-23-2010 18:35:34.447', '1:15',1)
insert into #PRT_MCP1_Flt values ('MCP1_EStop02', '04-23-2010 18:35:34.447', '2:15',1)
insert into #PRT_MCP1_Flt values ('MCP1_EStop03', '04-23-2010 18:35:34.447', '3:15',1)
insert into #PRT_MCP1_Flt values ('MCP1_EStop04', '04-23-2010 18:35:34.447', '4:15',1)
insert into #PRT_MCP1_Flt values ('MCP1_EStop05', '04-23-2010 18:35:34.447', '5:15',1)

insert into #PRT_MCP1_Flt values ('MCP1_MotorFlt01', '04-23-2010 18:35:34.447', '3:00',1)
insert into #PRT_MCP1_Flt values ('MCP1_MotorFlt02', '04-23-2010 18:35:34.447', '3:00',1)
insert into #PRT_MCP1_Flt values ('MCP1_MotorFlt03', '04-23-2010 18:35:34.447', '3:00',1)
insert into #PRT_MCP1_Flt values ('MCP1_MotorFlt04', '04-23-2010 18:35:34.447', '3:00',1)
insert into #PRT_MCP1_Flt values ('MCP1_MotorFlt05', '04-23-2010 18:35:34.447', '3:00',1)

insert into #PRT_MCP1_Flt values ('MCP1_PanelFlt01', '04-23-2010 18:35:34.447', '1:00',1)
insert into #PRT_MCP1_Flt values ('MCP1_PanelFlt02', '04-23-2010 18:35:34.447', '2:00',1)
insert into #PRT_MCP1_Flt values ('MCP1_PanelFlt03', '04-23-2010 18:35:34.447', '3:00',1)
insert into #PRT_MCP1_Flt values ('MCP1_PanelFlt04', '04-23-2010 18:35:34.447', '4:00',1)
insert into #PRT_MCP1_Flt values ('MCP1_PanelFlt05', '04-23-2010 18:35:34.447', '5:00',1)

insert into #PRT_MCP1_Flt values ('MCP1_PEJam01', '04-23-2010 18:35:34.447', '5:15',1)
insert into #PRT_MCP1_Flt values ('MCP1_PEJam02', '04-23-2010 18:35:34.447', '5:15',1)
insert into #PRT_MCP1_Flt values ('MCP1_PEJam03', '04-23-2010 18:35:34.447', '5:15',1)
insert into #PRT_MCP1_Flt values ('MCP1_PEJam04', '04-23-2010 18:35:34.447', '5:15',1)
insert into #PRT_MCP1_Flt values ('MCP1_PEJam05', '04-23-2010 18:35:34.447', '5:15',1)

SELECT
LEFT(Fault,LEN(Fault)-2) AS Fault,
COUNT(*) AS FaultCount,
SUM(DATEDIFF(n,0,Duration)) / 60.0 AS TotalDuration
FROM #PRT_MCP1_Flt
GROUP BY LEFT(Fault,LEN(Fault)-2)

IF OBJECT_ID('tempdb..#PRT_MCP1_Flt') IS NOT NULL DROP TABLE #PRT_MCP1_Flt;

/*
Fault FaultCount TotalDuration
MCP1_CommFlt 5 11.250000
MCP1_EStop 5 16.250000
MCP1_MotorFlt 5 15.000000
MCP1_PanelFlt 5 15.000000
MCP1_PEJam 5 26.250000
--*/

AlanPBates
Starting Member

34 Posts

Posted - 2010-05-25 : 14:46:13

lazerath,

Thank you ! I was working on the same lines ... Thank you ...

Alan.

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2010-05-25 : 14:49:53

that query returns a decimal value representing total hours. Note 11.25 instead of 11.15 (11 hours 15 minutes).

This query will return a TIME datatype instead:

SELECT
LEFT(Fault,LEN(Fault)-2) AS Fault,
COUNT(*) AS FaultCount,
CONVERT(TIME,DATEADD(s,SUM(DATEDIFF(S,0,Duration)),0)) AS TotalDuration
FROM #PRT_MCP1_Flt
GROUP BY LEFT(Fault,LEN(Fault)-2)

/*
Fault FaultCount TotalDuration
MCP1_CommFlt 5 11:15:00.0000000
MCP1_EStop 5 16:15:00.0000000
MCP1_MotorFlt 5 15:00:00.0000000
MCP1_PanelFlt 5 15:00:00.0000000
MCP1_PEJam 5 02:15:00.0000000
--*/

Maybe there's an easier way, but that's what I came up with for my first time working with a TIME datatype.

AlanPBates
Starting Member

34 Posts

Posted - 2010-05-25 : 14:58:02

I modified it to -

SELECT
LEFT(Fault,LEN(Fault)-2),
COUNT(*) AS FaultCount,
SUM(DATEDIFF(SS,0,Duration))
FROM PRT_MCP1_Flt
GROUP BY LEFT(Fault,LEN(Fault)-2)

This got me the desired results !

Thanks,

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources