I have a really basic question. The following SQL query works for me:
Select EnterUserID, Enterdate
from tblCsEventReminders
where EnterDate >= Convert(datetime, '2015-04-01')
I am essentially trying to write a query to count the number of user logins after a certain date, so I need to count 'EnterUserID' but I am having problems getting the count() function to work.
I want my query to list all SSNS that have more than one record in the table. I have this query:
Code:
SELECT SSN, name4, count(*) from [1099_PER]
group by SSN, name4
having count(SSN) > 1
It does retrieve the right SSNS and tells me how many times the SSN occurs in the table. However, I want my query results to display their full records.
For example
SSN NAME4 COUNT
123445555 WALTER - 4
I want the query to show me all four records for this SSN. I thought removing the count field would do this, but it still gives me only one instance of each SSN.
is it possible to use the window functions to count the number of rows in the previous 24hours from the current row.
I have a table of events like:
User, TimeStamp, etc...
I want to identify the row which is the event number 50 in the past 24 hours.
does the window functions can do this? and how?
the ROW PRECEDING etc... appear to not have enough feature to support time related function.
Stream-insight is able to create this type of query, but I have to do it directly in SQL.
below data,
Countery
parentid
CustomerSkId
sales
A
29097
29097
10
A
29465
29465
30
A
30492
30492
40
[code]....
Output
Countery
parentCount
A
8
B
3
c
3
in my count function,my code look like,
set buyerset as exists(dimcustomer.leval02.allmembers,custoertypeisRetailers,"Sales")
set saleset(buyerset)
set custdimensionfilter as {custdimensionmemb1,custdimensionmemb2,custdimensionmemb3,custdimensionmemb4}
set finalset as exists(salest,custdimensionfilter,"Sales")
Set ProdIP as dimproduct.dimproduct.prod1
set Othersset as (cyears,ProdIP)
(exists(([FINALSET],Othersset,dimension2.dimension2.item3),[DimCustomerBuyer].[ParentPostalCode].currentmember, "factsales")).count
it will take 12 to 15 min to execute.
Hi;
I know it must be really easy but I couldn't somehow figure it out:
3 columns: pms_ID, pms_UserID, pms_FlagSeen 1 ab1 True 2 ab1 True 3 ab1 True 4 ab1 False 5 ab2 True 6 ab2 False
All I want is to count number of Falses and Total (Trues and Falses) belonging to Users (I mean by pms_UserID)
Any help would be appreciated..
Regards...
Hi, i am using MSSQL 2005 and i am trying to count the number of rows in a table that is null. The problem is that its returning 0 even doh its several that is null.
SELECT count(*) as nr FROM forum_board WHERE (owner = null)
have also tried
SELECT count(*) as nr FROM forum_board WHERE (owner = 'null')
still returns 0.
I know this is simple but my minds gone blank
id desc
12345 Item 1
12345 Item 2
12346 Item 3
12346 Item 4
12347 Item 5
I want to count all the distinct ID's, so for the results here I need
id count
12345 2
12346 2
12347 1
Thanks
In Sql Server
Code Snippet
CREATE TABLE t_contact
(
Id uniqueidentifier,
FirstName nvarchar(50),
LastName nvarchar(50),
TaskId uniqueidentifier
)
GO
CREATE TABLE t_task
(
Id uniqueidentifier,
Start datetime
)
GO
INSERT INTO t_task (Start, Id) VALUES ('3/25/2007 12:00:00 AM', '5949b899-3230-4d30-b210-9903015b2c6b')
INSERT INTO t_contact (FirstName, LastName, TaskId, Id) VALUES ('Adam', 'Tybor', '5949b899-3230-4d30-b210-9903015b2c6b', '304fc653-d366-404b-878d-9903015b2c6f');
INSERT INTO t_task (Start, Id) VALUES ('4/1/2007 12:00:00 AM', '4bd2df60-ca6c-493d-8824-9903015b2c6f')
INSERT INTO t_contact (FirstName, LastName, TaskId, Id) VALUES ('John', 'Doe', '4bd2df60-ca6c-493d-8824-9903015b2c6f', '7b91f7d6-d71e-47b4-a7ec-9903015b2c6f')
INSERT INTO t_task (Start, Id) VALUES ('3/29/2007 12:00:00 AM', '05167e74-cf63-452a-8f25-9903015b2c6f')
INSERT INTO t_contact (FirstName, LastName, TaskId, Id) VALUES ('Jane', 'Doe', '05167e74-cf63-452a-8f25-9903015b2c6f', '6871ee8d-bc83-478c-8a7c-9903015b2c6f')
GO
SELECT task1_.Start as y0_, count(this_.FirstName) as y1_ FROM t_contact this_ inner join t_task task1_ on this_.TaskId=task1_.Id GROUP BY task1_.Start
GO
Result (Expected)
2007-03-25 00:00:00.000 1
2007-03-29 00:00:00.000 1
2007-04-01 00:00:00.000 1
Result In Sql CE (UnExpected)
2007-03-25 00:00:00.000 3
2007-03-29 00:00:00.000 3
2007-04-01 00:00:00.000 3
Can SQL CE not count with a join? Seems like this a bug with aggregates or joins. I tried everything to try and get the correct result but no luck.
Thanks Adam
Hello
I'm trying to do a simple =Sum([unitPrice] * [quantity]) but it won't doing anything more than stay on zero! I've put it in the properties but has anyone got any ideas to get the total of this???
I have a cube with 8 dimensions: time, organization, customer, item, region, market, salesperson, shipped_from
3 Measures: invoice_qty, invoice_price, sales_total
I want to get the average invoice_price for an item ...across any dimension or groups of dimensions... what is the proper syntax for creating a calculated member using the dims and measures above
Thanks in advance
Steve
Hello all. I have an Access table with EmpName, JobTask, and Hours. Multiple lines can contain the same Employee with the same JobTask. What I need to do is to list the Employee, JobTasks (grouped), and summed hours for each JobTask.
DB Ex:
John Doe Welding 8
John Doe Cleaning 4
Bubba Smith Fork Lift 3
John Doe Welding 7
Steve Johnson Welding 5
Bubba Smith Fork Lift 6
Page output:
John Doe
Welding 15 hrs.
Cleaning 4 hrs.
Bubba Smith
Fork Lift 9 hrs.
Steve Johnson
Welding 5 hrs.
This is how I figured it should be, but it's not working for me:
SELECT EmpName, SUM (Hours), JobTask
FROM tblEmpTime
Group By JobTask
Any suggestions would be greatly appreciated.
Thanks,
Parallon
Hey all,
Very simple question from a real n00b: In many of my stored procedures I am repeating a CASE statement where I'm replacing null values with zero, like this:
(CASE WHEN @Field IS NULL THEN 0 ELSE @Field END)
I have many SPs where I have that code repeated literally dozens of times. Is there any performance benefit to creating a function to perform this task and using that instead of the repeated CASE statements? Or am I trying to be too clever?
Thanks!
Ron Moses
ConEst Software Systems
Hi, I need to write a simple function to format the contents of the fields in my table. I bascially want to say that if the value in a field is below '0' then format the text in colour red and if the value in the field is 0 or above then format it in black. Obviously this can be done by writing an expression in each field but i would prefer to write a function - any ideas.....
View 1 Replies View RelatedHi All,
Does anyone know how to return a date the sql query analyser like (Aug 2, 2004)
Right now, the following statement returns (Aug 2, 2004 8:40PM). This is now good because I need to do a specific date search that doesn't include the time.
Many thanks in advance!!
Brad
----------------------------------------------
declare @today DateTime
Select @today = GetDate()
print @today
Hi All!This is the first time i am tryin to write a sql server 2000 function.I need to clean up some of my stored procedures..Can any one please give me skeleton for a userdefined function orcorrect my function. I get the following error.Select statements included within a function cannot return data to aclient.This returns a bit and it does a query to the database to decide thevalue of the bitCREATE FUNCTION GoodAd(@adid int,@currentdate datetime)RETURNS bitASBEGINdeclare @Return bitselect @return = 1if exists (select null from adqas where adid=@adid)select @return 0return @returnend
View 2 Replies View RelatedDear all,
I have a table with the the following columns:
ProductCode varchar(50) PK,
ProductType varchar(50),
ProductCategory varchar(50),
InStock (bit),
Locked (bit)
What I want to do is to query this table to return me a list of product type and category, and the total number of products in each category, and the count of number of instock, and locked.
i.e. Type, Category, TotalCount, NumberInStock, NumberNoStock, NumberLocked, NumberUnlocked.
How do I do this in a query?
Any help is very much appreciated.
Eugene
How can know I how may records are there in a table with out using count(*)
Regards
Reddy
I need to perform a summation on the results of a COUNT but I'm not sure what's the best way to do it.
Example:
TableA
---------------
CaseID (int)
FollowupCorrespondence (int) (not guaranteed to be sequential)
CustomerName (varchar)
/**Get the total number of followups for each customer case, and display the customer's name.*/
select T.caseid, sum(T.cnt), T.CustomerName,
T.FollowupCorrespondence
from (select CaseID, count(FollowupCorrespondence)
as cnt, CustomerName,
FollowupCorrespondence
from tableA
group by caseID, CustomerName,
FollowupCorrespondence;
) as T
group by caseid, customername, followupcorrespondence
I get the following output, but what I need is to get '4' in each row of the SumCases column.
CASEID SUMCases CUSTOMERNAME FOLLOWUPCORRESPONDENCE
----------------------------------------------------------------------------------------------------------------
1.................1.............John Doe................1
1.................1.............John Doe................2
1.................1.............John Doe................3
1.................1.............John Doe................4
Hi,
I am new to sql language so bear with me.
Suppose I have a relation table Student(department, name, gender):
department, name, gender
Bio Elisa F
Physics Jeanne F
Physics Rocky M
Math Andy M
Math John M
and I want to find the number of male students in each department, how should I do it?
this is what I got so far:
select department, count(*) from student where gender like "M" group by department;
but the problem with this query is I will get this table
Department Count(*)
Physics 1
Math 2
What I want is
Department Count(*)
Bio 0
Physics 1
Math 2
Can someone help with this? Thank you
I totally forgot how can I make a percentage. Look at the code below:
create table #acca (name varchar(10), tipo varchar(10), lett int)
insert into #acca values ('Italy','Europe',15), ('France','Europe',10), ('Colombia','America',15), ('Cile','America',75)
select * from #acca
/*Query Number 1 */
select name, tipo, lett, sum(lett) over (partition by tipo) as TotCon,
sum(lett) as Total
from #acca group by name,tipo,lett;
/*Query Number 2*/
with cte as (
select name, tipo, lett, sum(lett) over (partition by tipo) as TotCon,
sum(lett) as Total
from #acca group by name,tipo,lett)
select name, tipo, lett/totcon from cte
Question query number 1: how can I retrieve the absolute total? Sum(lett) over what?
Question query number 2: why lett / totcon retrieves 0?
Question plus: is there a way to retrieve the percentage without using the cte?
OK I am having problems trying to figure out or if it is even possible to do this in MSSQL
Here is my records
1=New
2=Open
3=Closed
Table stats
ID, Status
1 1
2 1
3 1
4 2
5 2
6 3
So I am trying to count each of the status
So Total records should be 6, New 3, Open 2, Closed 1 records.
SELECT COUNT(*) FROM Stats
Thats about all I know on this, will give me the total. but is there a way to count the total and count individuals.
Hi guys. Does anyone know if there is any function to count the length of a variable?
Thank you very much.
Guys,
I'm using the following code and I just want display all glcodes that have a count less than 2:
select glcode,
sum(abs(sysvalue)) 'Movement',
count(glcode) 'Occurances'
from jet
group by glcode
order by occurances
The following sytax works i know i need to use the where function. Does anyone know the syntax?
Cheers
Michael
Hello,
I have created the following query... and need to get
the total records display for my report. I have tried
adding in the count(*) function to my select list, but I get
errors. Any help is appreciated.
SELECT
A.ParentSubjectName,
A.ParentSubject,
A.SubjectId,
B.CreatedOn
FROM dbo.Subject A
INNER JOIN dbo.Incident B ON A.SubjectId = B.SubjectId
WHERE A.ParentSubjectName LIKE 'ACDelco Products%'
AND (B.CreatedOn >= '2007-01-01' AND B.CreatedOn <= '2007-11-30')
AND A.SubjectId IN
(
'C44ADE3E-527B-DC11-8A2D-00170857BDE7',
'F8758E52-527B-DC11-8A2D-00170857BDE7',
'7E65F458-527B-DC11-8A2D-00170857BDE7',
'7F65F458-527B-DC11-8A2D-00170857BDE7',
'2BE35262-527B-DC11-8A2D-00170857BDE7',
'2AE35262-527B-DC11-8A2D-00170857BDE7',
'A2002127-527B-DC11-8A2D-00170857BDE7',
'41A8A66F-527B-DC11-8A2D-00170857BDE7',
'A3002127-527B-DC11-8A2D-00170857BDE7',
'D6C08B45-527B-DC11-8A2D-00170857BDE7',
'C439FB4B-527B-DC11-8A2D-00170857BDE7'
)
ORDER BY B.CreatedOn[/blue]
create function mytotalcount
(@audit varchar(50), @startdate datetime, @enddate datetime)
returns table
as
return
(
select t.value,sum(t.countvalue) as totalcount from
(
select
sm.value,count(sm.value) as countvalue
from subjectbase s
join stringmap sm
on s.organizationid = sm.organizationid
inner join audit a
on s.subjectid=a.subjectid
inner join incidentbase i
on i.subjectid=s.subjectid
where a.auditid= @audit and (i.modifiedon between @startdate and @enddate) and
sm.attributename = 'contractservicelevelcode' and
sm.ObjectTypeCode = 112
group by sm.value
) t
group by t.value
)
value totalcount
------------------
NHLBI Employee329
NIH Employee329
Public329
VIP329
instead of different values i m getting same...
there is something wrong in joins..can anyone help me?
thanks.
I am creating a report that will print the customer's Invoices grouped by SalesPerson. I created a variable in the dataset so that it sets to the status of the invoce to Paid, Not Paid and Partial. All is working fine so far.
I have a groupfooter for each salesperson where I want to print the total no. of invoices for that SalesPerson that are PAID, NONPAID and Partaial.
So on the expressions of that fields, I have the following code. It allows me to print the report, but it prints #Error for the value of thoses field
I also want to print the the same fields in the report total area.
Any ideas ??
=Count(Fields!Document_No_.Value)having (Fields!InvStatus.Value = "PAID")
=Count(Fields!Document_No_.Value)having (Fields!InvStatus.Value = "UNPAID")
=Count(Fields!Document_No_.Value)having (Fields!InvStatus.Value = "PARTIAL")
I am trying to divide 2 funcitons by each other and I am coming back with 0 as my result in one of them while I am getting the correct answer in another. The two columns I am trying to pull with functions are:
SELECT
sum(D.PrincipalBal)/sum(L.PrincipalBal)*100 as DelqRatioByBal
, count(D.LoanID)/count(L.LoanID)*100 as DelqRatioByCnt
FROM Loan L
INNER JOIN DelqINFO D on D.LoanID = L.LoanID
I get the correct result back for 'DelqRatioByBal' but I get 0 back for 'DelqRatioByCnt'
Any suggestons?
How do you know when you should use COUNT and when to use SUM functions?
View 12 Replies View RelatedI have the following query and want the results within my query although I want to be able to get the SUM of my COUNT results. So basically I want the SUM of [Document Count] within my query/
DECLARE @Date date;
SET @date = '02/05/2015'
SELECT CONVERT(VARCHAR,CONVERT(Date, @Date, 103),101) AS [Date Scanned], (SELECT COUNT(*) FROM QUEUE WHERE date_scanned = @Date) AS [Total Scanned],
COUNT(dbc_state) AS [Document Count],
CASE
WHEN dbc_state = 1 AND ON_HOLD = '1' THEN 'On Hold'
[code]....
Hi,
We are seeing memory issues with a simple C# based TVF that splits an input string into a table based a delimiter. Entries such as the following show up in the SQL log:
AppDomain 8 (DBName.dbo[runtime].7) is marked for unload due to memory pressure.
AppDomain 8 (DBName.dbo[runtime].7) unloaded.
These entries only show up after the TVF has been called many times (~ half million times or more).
We encountered the same memory issues with June CTP, but they appeared to be fixed in Sepetmber CTP. Somehow the issues come back for us in the SQL Server 2005 RTM version. With June CTP after these errors show up many many times, the SQL server had to be re-started.
I'd appreciate any comments/ideas regarding these error messages in the SQL log?
We are using the RTM version of SQL2005: Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)
Oct 14 2005 00:33:37
Copyright (c) 1988-2005 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
Thanks,
Wenbin Zhang
I have created an assembly with permission set safe, and a function inside the assembly.
The function reads data from the same SQL Server as it is running inside. For connection I use the "Context connection = true", and the function has the SystemDataAccessKind attribute set to Read.
However when I execute my CLR function I get an error saying something like:
"The request for permission of type System.Data.SqlClient.SqlClientPermission...... failed"
I do, as the login user, have been granted the necessary rights, so I don't believe this is the answer to the error.
And my .dll is also signed.
Has this something to do with writing something a config file?
I have had simular problems with reporting services but fixed them by entering a node in the rspolicy.config file. If this is the case here - which .config file should i modify...machine.config?
I have a transaction number in my mapping table. I have a matching transaction number in my PDHist table. Sometimes I have matching transaction numbers in my PD table, but not always. This is causing no records to be returned. I have a One to Many relationship between my mapping table and both PD and PDHist.
Also, I need to check for nulls in my foreign exchange table.I can’t post the SQL because this is a classified project. However, it should be something like this, I think.
IIf(IsNull([Redem]![FX Rate]),([PDHist]![Remaining Balance]+[PD]![Closing Balance(TC)]).
The addition isn’t working. I think with a small push I can get this straightened out.
I need a function to count business days (exclude Sat, Sun) that I can call within a view. I would rather not build a "calendar table" this will be used ongoing for years into the future.
Does anyone have anything like this they could share? If there is another source you could direct me to I would appreciate that as well.
TIA
Phil