SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 count and group by name
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

magmo
Aged Yak Warrior

526 Posts

Posted - 03/28/2013 :  08:14:13  Show Profile  Reply with Quote
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

Sweden
30281 Posts

Posted - 03/28/2013 :  08:23:34  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page

magmo
Aged Yak Warrior

526 Posts

Posted - 03/28/2013 :  08:26:14  Show Profile  Reply with Quote
Sweet, Thanks Peso!
Go to Top of Page

magmo
Aged Yak Warrior

526 Posts

Posted - 03/28/2013 :  12:01:15  Show Profile  Reply with Quote
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?



Edited by - magmo on 03/28/2013 12:08:09
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30281 Posts

Posted - 03/28/2013 :  15:16:23  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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



N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

magmo
Aged Yak Warrior

526 Posts

Posted - 03/28/2013 :  15:29:22  Show Profile  Reply with Quote
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..
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2223 Posts

Posted - 03/29/2013 :  01:09:03  Show Profile  Reply with Quote
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
Go to Top of Page

magmo
Aged Yak Warrior

526 Posts

Posted - 03/29/2013 :  05:13:11  Show Profile  Reply with Quote
Thanks that works just right :)
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2223 Posts

Posted - 03/29/2013 :  05:35:16  Show Profile  Reply with Quote
quote:
Originally posted by magmo

Thanks that works just right :)


welcome

--
Chandu
Go to Top of Page

magmo
Aged Yak Warrior

526 Posts

Posted - 04/10/2013 :  11:17:20  Show Profile  Reply with Quote
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"?
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2223 Posts

Posted - 04/11/2013 :  01:45:52  Show Profile  Reply with Quote
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?
Go to Top of Page

magmo
Aged Yak Warrior

526 Posts

Posted - 04/11/2013 :  02:13:51  Show Profile  Reply with Quote
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"

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000