Count() function to count the records in specific column in Sql server 2008

Hi,

I have a table with 10 columns, and i am doing a count on selected columns.
SELECT count 
	([User_Emp_ID], [User_ID], [User_Name],
	[Reviewer_ID], [High_Privileged], [Certifiable], [Bypass_Termination])
from OIA_Data_Load
GO

Open in new window


But i am geting an error
Msg 174, Level 15, State 1, Line 4
The count function requires 1 argument(s).

Open in new window


Also, in my table, i have  column called Count. I want this column to count the no. of rows in these specified columns, and get displayed at the end of the column.
msrblr4Asked:
Who is Participating?
 
DALSOMCommented:
Try this, hope this really helps you find your answer :

create table #alldates(dt datetime)
declare @dd int
set @dd = 1
insert into #alldates values(GETDATE())
while @dd <= 1000
begin
      insert into #alldates values(DATEADD(dd,(@dd*-1),getdate()))
      set @dd = @dd + 1
end

create table #invoices(invoiceno int not null,inv_date datetime,amount money,sold bit)
insert into #invoices values(1,'2011-12-25',100,0)
insert into #invoices values(2,'2011-12-25',200,0)
insert into #invoices values(3,'2011-12-22',100,0)
insert into #invoices values(5,'2011-12-21',100,0)
insert into #invoices values(8,'2011-12-20',100,0)


select d.dt as [Invoice Date],COUNT(i.inv_date) as [Invoice counts]
from #alldates d left outer join #invoices i on year(d.dt) = year(i.inv_date)
      and MONTH(d.dt) = MONTH(i.inv_date) and DAY(d.dt) = DAY(i.inv_date)
where YEAR(d.dt) = 2011 and MONTH(d.dt) = 12 and DAY(d.dt) >= 20
group by d.dt


Check the group by, count field and joins.
And of course, the results.
By the way,
Merry C.
Dalsom
0
 
TempDBACommented:
you can't specify more then 1 columns in count() function. You can do it by
SELECT count (*)
from OIA_Data_Load
GO
0
 
Christopher GordonSenior Developer AnalystCommented:
Write a subquery to get the distinct record count.  Then do a count off of that subquery.

SELECT count(*) 
(	
	select distinct 

	[User_Emp_ID], [User_ID], [User_Name],
	[Reviewer_ID], [High_Privileged], [Certifiable], [Bypass_Termination]
	
	from OIA_Data_Load	
) distinct_fields

GO

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
tim_csCommented:
Can you give an example of what your data looks like and what your expected results are?

Are you looking to see which fields have values?
0
 
DALSOMCommented:
Hi, try this :

select field1,field2,fieldn,count(*) as fieldcount
from mytable
where <myfilters>
group by field1,field2,fieldn

In this way, you will count only your "group by" fields.

0
 
msrblr4Author Commented:
I have attached the file for your reference.
I have shown the columns, which need a count on them, and at the end, i have another column named "Record_Count".
The script should only count the total no. of records which are higlighted in Green, and display them at the end of the "Record_Count" column *(as shown in the xls file).

Any script for this, would be good, so that i can add it into my existing SP.
OIA-Data.xls
0
 
SickSkilzCommented:
Just do it with a subquery
select count(*) from
(select blaa blaa blaa from blaa)

This way you can get counts of distinct members across different fields for isntance.
0
 
DALSOMCommented:
You will have the 24 repeated as many rows is counting for record_count field. Not only for last one.
But you can supress at your program where you need to show your query results.

I hope this help, but, you really have all good answers from everyone posted here! ;)

0
 
msrblr4Author Commented:
Actually, i wrote the script and it looks like this;
SELECT User_Emp_ID, User_ID, User_Name,
	Reviewer_ID, High_Privileged, Certifiable, Bypass_Termination, count(*) AS Record_Count
	from OIA_Data_Load
	GROUP BY 
	GROUPING SETS 
	(
		(User_Emp_ID, User_ID, User_Name,
		Reviewer_ID, High_Privileged, Certifiable, Bypass_Termination), () 
	)
GO

Open in new window

This gives me the result outside the table. i.e. it fetches the records and displays the total record counts.
How do i update the same, which will update my table and the records in them, and shows me the final result......
0
 
SickSkilzCommented:
so here it is

select  User_Emp_ID, Application_Role,User_ID,User_Name,Reviewer_ID,      High_Privileged,Certifiable, Bypass_Termination, count(*) as Record_Count

That would show in the last column a count would would represent the number of records specific to a distinct combination of all the green column fields.
0
 
msrblr4Author Commented:
Not really up to the acceptance level, but i got what i want.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.