asked
Help in Select to get total value depends on columns
Dear All, If I have a table with the following column : **GOV_id, GOV_name, VR_TYPE** and this is the code to Create this table : - CREATE TABLE [dbo].[TT1]( [GOV_id] [int] NULL, [GOV_name] [varchar](50) NULL, [VR_TYPE] [char](3) NULL ) ON [PRIMARY] GO After I did several insert :- INSERT [dbo].[TT1] ([GOV_id], [GOV_name], [VR_TYPE]) VALUES (1, N'USA', N'ADD') INSERT [dbo].[TT1] ([GOV_id], [GOV_name], [VR_TYPE]) VALUES (1, N'USA', N'DEL') INSERT [dbo].[TT1] ([GOV_id], [GOV_name], [VR_TYPE]) VALUES (2, N'FRA', N'ADD') INSERT [dbo].[TT1] ([GOV_id], [GOV_name], [VR_TYPE]) VALUES (2, N'FRA', N'DEL') INSERT [dbo].[TT1] ([GOV_id], [GOV_name], [VR_TYPE]) VALUES (2, N'FRA', N'COR') INSERT [dbo].[TT1] ([GOV_id], [GOV_name], [VR_TYPE]) VALUES (3, N'GER', N'ADD') INSERT [dbo].[TT1] ([GOV_id], [GOV_name], [VR_TYPE]) VALUES (3, N'GER', N'DEL') INSERT [dbo].[TT1] ([GOV_id], [GOV_name], [VR_TYPE]) VALUES (1, N'USA', NULL) INSERT [dbo].[TT1] ([GOV_id], [GOV_name], [VR_TYPE]) VALUES (1, N'USA', NULL) INSERT [dbo].[TT1] ([GOV_id], [GOV_name], [VR_TYPE]) VALUES (2, N'FRA', NULL) INSERT [dbo].[TT1] ([GOV_id], [GOV_name], [VR_TYPE]) VALUES (2, N'FRA', N'ADD') INSERT [dbo].[TT1] ([GOV_id], [GOV_name], [VR_TYPE]) VALUES (2, N'FRA', N'ADD') INSERT [dbo].[TT1] ([GOV_id], [GOV_name], [VR_TYPE]) VALUES (1, N'USA', N'DEL') I did this query to make a report : I need to know homw many vistoer did **ADD**,**DEL**, or **COR** depends on ***GOV_name and VR_TYPE is not null*** SELECT COUNT(*) AS Total, GOV_name, VR_TYPE FROM TT1 WHERE (NOT (VR_TYPE IS NULL)) GROUP BY GOV_name, VR_TYPE ![alt text][1] My question how can I write a query to make my result like that :- ![alt text][2] [1]: /storage/temp/603-report_1.jpg [2]: /storage/temp/604-report2.jpg I'm using SQL SERVER 2008
If it's an option to use Reporting Services, you could look at using a matrix within a report. If you just need the SQL to output as you've described here, you'll need to use PIVOT, described [here][1] In your particular example, you want something like this: select [GOV_name],[ADD],[COR],[DEL] from [dbo].[TT1] pivot ( count([GOV_id]) for VR_TYPE in ([ADD],[COR],[DEL]) ) as pvt [1]:
http://msdn.microsoft.com/en-gb/library/ms177410(v=sql.105).aspx
@troy_2012 - why not follow the link that @tomgough79 provided and see if you can make the changes you need to incorporate a WHERE clause in the query?
Rather than "from [dbo].[TT1]" you could use "from <select statement > "
Sorry for the confusion... this is what I need SELECT COUNT(*) AS Total, GOV_name, VR_TYPE FROM TT1 WHERE (NOT (VR_TYPE IS NULL)) and IS_valid = 'A' GROUP BY GOV_name, VR_TYPE I forgot to put the **IS__valid**.....How can I put the Where clause for **IS__valid** = 'A'
I did the query ,Thank you agotomgough79 select * from ( select [GOV_name],VR_TYPE,GOV_id from [dbo].[TT1] where IS__valid='A' ) up pivot ( count([GOV_id]) for VR_TYPE in ([ADD],[COR],[DEL]) ) as pvt
12 People are following this question.
