count and group by name - 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 2005 Forums

Transact-SQL (2005)

count and group by name

Author

Topic

magmo
Aged Yak Warrior

558 Posts

Posted - 2013-03-28 : 08:14:13

Hi I have the following data...



USE [test]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[EventCards](
	[ID] [bigint] IDENTITY(1,1) NOT NULL,
	[GUID] [nvarchar](100) NULL,
	[EventName] [nvarchar](100) NULL,
	[XmlFilename] [nvarchar](100) NULL,
	[PdfFileName] [nvarchar](100) NULL,
	[DateAdded] [datetime] NOT NULL,
 CONSTRAINT [PK_ID] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET IDENTITY_INSERT [dbo].[EventCards] ON 

GO
INSERT [dbo].[EventCards] ([ID], [GUID], [EventName], [XmlFilename], [PdfFileName], [DateAdded]) VALUES (10, N'5CCDA13B-1098-4CED-953D-184FFCE0E363', NULL, NULL, N'5CCDA13B-1098-4CED-953D-184FFCE0E363.pdf', CAST(0x0000A18D0114055F AS DateTime))
GO
INSERT [dbo].[EventCards] ([ID], [GUID], [EventName], [XmlFilename], [PdfFileName], [DateAdded]) VALUES (11, N'5CCDA13B-1098-4CED-953D-184FFCE0E363', N'testevent', N'5CCDA13B-1098-4CED-953D-184FFCE0E363.xml', N'', CAST(0x0000A18D01140574 AS DateTime))
GO
INSERT [dbo].[EventCards] ([ID], [GUID], [EventName], [XmlFilename], [PdfFileName], [DateAdded]) VALUES (12, N'69B145E5-A9FF-476C-BA68-74414DF8BDD7', NULL, NULL, N'69B145E5-A9FF-476C-BA68-74414DF8BDD7.pdf', CAST(0x0000A18D0116A25C AS DateTime))
GO
INSERT [dbo].[EventCards] ([ID], [GUID], [EventName], [XmlFilename], [PdfFileName], [DateAdded]) VALUES (13, N'69B145E5-A9FF-476C-BA68-74414DF8BDD7', N'testevent', N'69B145E5-A9FF-476C-BA68-74414DF8BDD7.xml', N'', CAST(0x0000A18D0116A2C8 AS DateTime))
GO
INSERT [dbo].[EventCards] ([ID], [GUID], [EventName], [XmlFilename], [PdfFileName], [DateAdded]) VALUES (14, N'07DD51DD-41B6-4F3D-AFE7-FABA8405CEDC', NULL, NULL, N'07DD51DD-41B6-4F3D-AFE7-FABA8405CEDC.pdf', CAST(0x0000A18D01189F0A AS DateTime))
GO
INSERT [dbo].[EventCards] ([ID], [GUID], [EventName], [XmlFilename], [PdfFileName], [DateAdded]) VALUES (15, N'07DD51DD-41B6-4F3D-AFE7-FABA8405CEDC', N'testevent2', N'07DD51DD-41B6-4F3D-AFE7-FABA8405CEDC.xml', N'', CAST(0x0000A18D01189F17 AS DateTime))
GO
INSERT [dbo].[EventCards] ([ID], [GUID], [EventName], [XmlFilename], [PdfFileName], [DateAdded]) VALUES (16, N'EC98FAE4-7233-4836-9E03-A008FA9ECCF2', NULL, NULL, N'EC98FAE4-7233-4836-9E03-A008FA9ECCF2.pdf', CAST(0x0000A18D0143EE9B AS DateTime))
GO
INSERT [dbo].[EventCards] ([ID], [GUID], [EventName], [XmlFilename], [PdfFileName], [DateAdded]) VALUES (17, N'EC98FAE4-7233-4836-9E03-A008FA9ECCF2', N'testevent2', N'EC98FAE4-7233-4836-9E03-A008FA9ECCF2.xml', N'', CAST(0x0000A18D0143EEA7 AS DateTime))
GO
INSERT [dbo].[EventCards] ([ID], [GUID], [EventName], [XmlFilename], [PdfFileName], [DateAdded]) VALUES (18, N'FFB62CAE-FEED-4F4B-BE8A-49A1900DDE43', NULL, NULL, N'FFB62CAE-FEED-4F4B-BE8A-49A1900DDE43.pdf', CAST(0x0000A18D0144A8CA AS DateTime))
GO
INSERT [dbo].[EventCards] ([ID], [GUID], [EventName], [XmlFilename], [PdfFileName], [DateAdded]) VALUES (19, N'FFB62CAE-FEED-4F4B-BE8A-49A1900DDE43', N'testevent2', N'FFB62CAE-FEED-4F4B-BE8A-49A1900DDE43.xml', N'', CAST(0x0000A18D0144A8D6 AS DateTime))
GO

SET IDENTITY_INSERT [dbo].[EventCards] OFF
GO

As you can see from the data the GUID number is the unique part here and each unique GUID number includes 2 rows, I would like a query that count how many Distinct GUID numbers there are grouped by the EventName Column. So in this case it should return .

Event Qty

testevent 2
testevent2 3

How do I do this?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-03-28 : 08:23:34

SELECT EventName, COUNT(DISTINCT GUID) FROM EventCards WHERE EventName IS NOT NULL GROUP BY EventName

N 56°04'39.26"
E 12°55'05.63"

magmo
Aged Yak Warrior

558 Posts

Posted - 2013-03-28 : 08:26:14

Sweet, Thanks Peso!

magmo
Aged Yak Warrior

558 Posts

Posted - 2013-03-28 : 12:01:15

Hi again

If I would like to group all the ones that doesn't have any eventname as "NA" (''), so that my result would be

Event Qty

NA 1
testevent 2
testevent2 3

if I also added this empty one..



INSERT [dbo].[EventCards] ([ID], [GUID], [EventName], [XmlFilename], [PdfFileName], [DateAdded]) VALUES (10, N'5CCDA13B-1098-4CED-953D-184FFCE0E369', NULL, NULL, N'5CCDA13B-1098-4CED-953D-184FFCE0E369.pdf', CAST(0x0000A18D0114055F AS DateTime))
GO
INSERT [dbo].[EventCards] ([ID], [GUID], [EventName], [XmlFilename], [PdfFileName], [DateAdded]) VALUES (11, N'5CCDA13B-1098-4CED-953D-184FFCE0E369', '', N'5CCDA13B-1098-4CED-953D-184FFCE0E369.xml', N'', CAST(0x0000A18D01140574 AS DateTime))
GO

What would I need to change then?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-03-28 : 15:16:23

[code]WITH cteSource(EventName, GUID)
AS (
SELECT CASE WHEN EventName > '' THEN EventName ELSE 'NA' END, GUID FROM dbo.EventCards
)
SELECT EventName, COUNT(DISTINCT GUID) FROM cteSource GROUP BY EventName[/code]

N 56°04'39.26"
E 12°55'05.63"

magmo
Aged Yak Warrior

558 Posts

Posted - 2013-03-28 : 15:29:22

Hi

I see now that this doesn't give me the correct result, it should only be classified as "NA" when EventName IS both null and/or ''. Otherwise it will count all the rows that IS NULL as "NA" and thats wrong..

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-29 : 01:09:03

[code]WITH cteSource(EventName, GUID)
AS
(
SELECT CASE WHEN EventName = '' THEN 'NA' ELSE EventName END, GUID FROM dbo.EventCards WHERE EventName IS NOT NULL
)
SELECT EventName, COUNT(DISTINCT GUID) FROM cteSource GROUP BY EventName
[/code]

magmo
Aged Yak Warrior

558 Posts

Posted - 2013-03-29 : 05:13:11

Thanks that works just right :)

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-29 : 05:35:16

quote:
Originally posted by magmo

Thanks that works just right :)

welcome

--
Chandu

magmo
Aged Yak Warrior

558 Posts

Posted - 2013-04-10 : 11:17:20

Hi again, I have come across an issue here. The rows thats inserted contains one row that have a PdfFileName and one XmlFilename, the solution I have here works fine as long as there are 2 rows and one of them have a value in the EventName column. But now only one row have been entered and that is one that have a PdfFileName but not a EventName, and that row is not counted as "NA" since its NULL, is it even possible to get that one classified as "NA"?

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-11 : 01:45:52

In the above solution, we are grouping records by EventName.... I think it won't affect PdfFileName , XmlFileName ..

Whats is the problem now? Can you explain with sample data?

magmo
Aged Yak Warrior

558 Posts

Posted - 2013-04-11 : 02:13:51

quote:
Originally posted by bandi

In the above solution, we are grouping records by EventName.... I think it won't affect PdfFileName , XmlFileName ..

Whats is the problem now? Can you explain with sample data?

Hi

Its grouped by eventname which works fine when there is a value, if everything works out as it should there should always be 2 rows that "belong" together, one row that have a PdfFileName and one that have XmlFileName (that row also contains the eventname). But now I've discovered that only one row was entered in the database (
one with PdfFileName but no eventname, and beacuse of this fact that row isn't counted as "NA"..


INSERT [dbo].[EventCards] ([ID], [GUID], [EventName], [XmlFilename], [PdfFileName], [DateAdded]) VALUES (10, N'5CCDA13B-1098-4CED-953D-184FFCE0E355', NULL, NULL, N'5CCDA13B-1098-4CED-953D-184FFCE0E355.pdf', CAST(0x0000A18D0114055F AS DateTime))

So if I enter this row its not counted, which is correct. It should count the EventNames, but if possible this row should also be counted as "NA"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources