Column counts - 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)

Column counts

Author

Topic

dhjackal
Starting Member

42 Posts

Posted - 2009-09-24 : 06:25:07

I'm trying to audit a documents table. The table and a sub set of test data is as follows ;


CREATE TABLE document_list(
	docnum	INT NOT NULL,
	docname VARCHAR(400) NOT NULL,
	activity VARCHAR(8) NOT NULL,
	document_version INT NOT NULL
)

INSERT INTO document_list (docnum, docname, activity, document_version) VALUES (872, 'Document number 872', 'Created', 1)
INSERT INTO document_list (docnum, docname, activity, document_version) VALUES (872, 'Document number 872', 'Opened', 1)
INSERT INTO document_list (docnum, docname, activity, document_version) VALUES (872, 'Document number 872', 'Viewed', 1)
INSERT INTO document_list (docnum, docname, activity, document_version) VALUES (872, 'Document number 872', 'Viewed', 1)
INSERT INTO document_list (docnum, docname, activity, document_version) VALUES (872, 'Document number 872', 'Viewed', 1)

INSERT INTO document_list (docnum, docname, activity, document_version) VALUES (761, 'Document number 761', 'Created', 1)
INSERT INTO document_list (docnum, docname, activity, document_version) VALUES (761, 'Document number 761', 'Viewed', 1)
INSERT INTO document_list (docnum, docname, activity, document_version) VALUES (761, 'Document number 761', 'Checkout', 1)
INSERT INTO document_list (docnum, docname, activity, document_version) VALUES (761, 'Document number 761', 'Created', 2)
INSERT INTO document_list (docnum, docname, activity, document_version) VALUES (761, 'Document number 761', 'Viewed', 2)
INSERT INTO document_list (docnum, docname, activity, document_version) VALUES (761, 'Document number 761', 'Checkout', 2)
INSERT INTO document_list (docnum, docname, activity, document_version) VALUES (761, 'Document number 761', 'Checkout', 2)
INSERT INTO document_list (docnum, docname, activity, document_version) VALUES (761, 'Document number 761', 'Viewed', 2)
INSERT INTO document_list (docnum, docname, activity, document_version) VALUES (761, 'Document number 761', 'Viewed', 2)
INSERT INTO document_list (docnum, docname, activity, document_version) VALUES (761, 'Document number 761', 'Viewed', 2)
INSERT INTO document_list (docnum, docname, activity, document_version) VALUES (761, 'Document number 761', 'Opened', 2)

What i want is a count of all the different types of activity for each document and it's version i.e.

Document number 761 version 2 was viewed 4 times, checked out 2 times and opened 1 time

All assistance / pointers in the right direction appreciated

sanoj_av
Posting Yak Master

118 Posts

Posted - 2009-09-24 : 06:32:32

Select
docnum, docname,document_version,activity,Count(1) as Activity_count
From
document_list
Group by
docnum, docname,document_version,activity