I need to create a drill down report with counts at each level, I cant use matrix, i need to implement this using SQL query..The format looks like below
I need to get count of the field employee id for each region 1 through 8 and for each status value
*, 0, 1 ,2 ,3 ,4
The following query returns a value of 0 for the unit percent when I do a count/subquery count. Is there a way to get the percent count using a subquery? Another section of the query using the sum() works.
Here is a test code snippet:
--Test Count/Count subquery
declare @Date datetime
set @date = '8/15/2007'
select -- count returns unit data Count(substring(m.PTNumber,3,3)) as PTCnt, -- count returns total for all units
(select Count(substring(m1.PTNumber,3,3))
from tblVGD1_Master m1
left join tblVGD1_ClassIII v1 on m1.SlotNum_ID = v1.SlotNum_ID
Where left(m1.PTNumber,2) = 'PT' and m1.Denom_ID <> 9
and v1.Act = 1 and m1.Active = 1 and v1.MnyPlyd <> 0
and not (v1.MnyPlyd = v1.MnyWon and v1.ActWin = 0)
and v1.[Date] between DateAdd(dd,-90,@Date) and @Date) as TotalCnt, -- attempting to calculate the percent by PTCnt/TotalCnt returns 0 (Count(substring(m.PTNumber,3,3)) /
(select Count(substring(m1.PTNumber,3,3))
from tblVGD1_Master m1
left join tblVGD1_ClassIII v1 on m1.SlotNum_ID = v1.SlotNum_ID
Where left(m1.PTNumber,2) = 'PT' and m1.Denom_ID <> 9
and v1.Act = 1 and m1.Active = 1 and v1.MnyPlyd <> 0
and not (v1.MnyPlyd = v1.MnyWon and v1.ActWin = 0)
and v1.[Date] between DateAdd(dd,-90,@Date) and @Date)) as AUPct -- main select
from tblVGD1_Master m
left join tblVGD1_ClassIII v on m.SlotNum_ID = v.SlotNum_ID
Where left(m.PTNumber,2) = 'PT' and m.Denom_ID <> 9
and v.Act = 1 and m.Active = 1 and v.MnyPlyd <> 0
and not (v.MnyPlyd = v.MnyWon and v.ActWin = 0)
and v.[Date] between DateAdd(dd,-90,@Date) and @Date
However, as you can see, the original select query is run twice and joined together.What I was hoping for is this to be done in the original query without the need to duplicate the original query.
With the function below, I receive this error:Error:Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0.Function:Public Shared Function DeleteMesssages(ByVal UserID As String, ByVal MessageIDs As List(Of String)) As Boolean Dim bSuccess As Boolean Dim MyConnection As SqlConnection = GetConnection() Dim cmd As New SqlCommand("", MyConnection) Dim i As Integer Dim fBeginTransCalled As Boolean = False 'messagetype 1 =internal messages Try ' ' Start transaction ' MyConnection.Open() cmd.CommandText = "BEGIN TRANSACTION" cmd.ExecuteNonQuery() fBeginTransCalled = True Dim obj As Object For i = 0 To MessageIDs.Count - 1 bSuccess = False 'delete userid-message reference cmd.CommandText = "DELETE FROM tblUsersAndMessages WHERE MessageID=@MessageID AND UserID=@UserID" cmd.Parameters.Add(New SqlParameter("@UserID", UserID)) cmd.Parameters.Add(New SqlParameter("@MessageID", MessageIDs(i).ToString)) cmd.ExecuteNonQuery() 'then delete the message itself if no other user has a reference cmd.CommandText = "SELECT COUNT(*) FROM tblUsersAndMessages WHERE MessageID=@MessageID1" cmd.Parameters.Add(New SqlParameter("@MessageID1", MessageIDs(i).ToString)) obj = cmd.ExecuteScalar If ((Not (obj) Is Nothing) _ AndAlso ((TypeOf (obj) Is Integer) _ AndAlso (CType(obj, Integer) > 0))) Then 'more references exist so do not delete message Else 'this is the only reference to the message so delete it permanently cmd.CommandText = "DELETE FROM tblMessages WHERE MessageID=@MessageID2" cmd.Parameters.Add(New SqlParameter("@MessageID2", MessageIDs(i).ToString)) cmd.ExecuteNonQuery() End If Next i ' ' End transaction ' cmd.CommandText = "COMMIT TRANSACTION" cmd.ExecuteNonQuery() bSuccess = True fBeginTransCalled = False Catch ex As Exception 'LOG ERROR GlobalFunctions.ReportError("MessageDAL:DeleteMessages", ex.Message) Finally If fBeginTransCalled Then Try cmd = New SqlCommand("ROLLBACK TRANSACTION", MyConnection) cmd.ExecuteNonQuery() Catch e As System.Exception End Try End If MyConnection.Close() End Try Return bSuccess End Function
I have a pretty simple query: Select title, avg(score) from votes group by title order by avg(score) Desc This returns a result like this: Abbey Road 4.0The White Album 3.5Meet the Beatles 3.0 The values in the score field are always 1, 2, 3, 4, or 5 What I really need from my query is 5 more columns (1, 2, 3, 4, and 5) with a count of how many votes each of those columns received. So the result set might be like this. Abbey Road 4.0 0 0 1 1 1 The White Album 3.5 0 1 0 0 1Meet the Beatles 3.0 2 2 2 2 2 I have tried to use the count function, but it counts all the values in the field. I can't figure out how to just count the values that match a certain criteria. When I tried to create subqueries, I got an error that said my subqueries where returning a multiple result sets. Any suggestions would be greatly appreciated, Chris
I am trying to count a column in my tbl. I have a table with the following, What i am trying to do is count the number of days of dtAttendance for an individual where bitpresent = 1. So this individual should have 2 days of attendance. I wrote some code belwo but it does not seem to count correctly, some it does and some it does not. intAssignedPersonnelID intUICID strSSN dtAttendance bitPresent intDrillStatus intMileage bitDatePayed210 1 333333333 5/26/2008 1 2 210 1 333333333 5/27/2008 1 2 210 1 333333333 5/28/2008 0 2
& "(select count(*) from saddotnet.dbo.tblAssignedPersonnel h where p.strSSN = h.strSSN and h.dtattendance <= p.dtattendance) " _ & "as count_attendance_as_at_dtattendance, p.dtattendance, bitpresent, intdrillstatus, " _ & "'<input name=chbxPresent' + convert(varchar(10), p.intAssignedPersonnelID) + ' type=checkbox>' as 'theCheckBox' " _ & "from cms.dbo.tblSIDPERS as s INNER JOIN " _ & "saddotnet.dbo.tblAssignedPersonnel as p on p.strSSN = s.sidstrSSN_SM where sidstrSSN_SM = left('" & SSN & "', 9)"
I have a table that is grouped by a date. Some records have the same date. I am just trying to return the total row count of the query.
Here is the sql:
SELECT ActiveWeek FROM wcWARS WHERE CreatedBy = 9034 AND FlagComplete = 1 GROUP BY ActiveWeek
I just want to return the total grouped column.... I thought Oracle had a ROWCOUNT function that returned the total rows of the query. I was unable to find one for SQL Server.
**Note: I already tried @@ROWCOUNT ~ This returns the total rows of the table.
Hello All, I have two tables A and B. A and B have column x in common. The relationship between A and B is one to many. I would like to count the number number of x's in table B and group by the x and then join this result back to table a. My attempt : SELECT COUNT(T1.x), T1.x, A.meeting_invite_idFROM (SELECT meeting_invite_idFROM REMINDER) T1, AWHERE A.meeting_invite_id *= T1.meeting_invite_idGROUP BY A.meeting_invite_id Anyone with any ideas.
assume that there are many stores listed in the table... how can i have the number of stores that has an ordercount?... ive tried some mdx queries but i still cant get it right...
I have two tables, Sales Headers and SalesLines. The SalesHeaders table will hold basic details of a sale, and the Sales Lines table will hold details of the items in the sale.
select distinct csd1.csd_orig + csd1.csd_subj + csd1.csd_type + csd1.csd_numb + csd1.csd_revi as "Document Reference No.", csd1.csd_labl + ' ' as "Description", csd1.csd_issu as "Docu Dt", csd1.csd_altr as "Alternate Number", csd2.csd_altr as "Transmittal Reference No.", csd2.csd_issu as "TIssu Dt", trd1.trd_recd as "TRecd Dt", apr1.apr_reqd as "RReqd Dt", case when apr1.apr_stat is null then 'Unknown / Pending / No Reply' else apc1.apc_libe end as "Document Status",
csd3.csd_altr as "Reply Reference No.", csd3.csd_issu as "RIssu Dt", trd2.trd_recd as "RRecd Dt"
order by csd1.csd_orig + csd1.csd_subj + csd1.csd_type + csd1.csd_numb + csd1.csd_revi
---
I need to count the resulting records of the query. How do i have to do this, could you please tell me ... I am new to SQl and I tried but could not solve it ...
( select emp_id, row_number() over (order by emp_id) as rownum from employee ) temp
where temp.rownum <=10
group by temp.emp_id
I would like to know whether there is a way to retrieve the no. of rows returned by the inner select query which could be displayed in the outer select query. I am not allowed to use temporary variables or tables variables for this purpose.
Hi I had posted this question earlier but could not get the solution, may be i was not clear with the doubts i had
I need to count the no of students for the different Intervention field which are like 14 different types against the field gender (male and Female )and field Ethnicity (horizontal field headers) 5 different types below
A student can be Male and Hispanic Type 1 also Male Hispanic Type 2
Male Hispanic Type 3
So his count is made in three places
I need to do this for the whole District level then for each Center under District level then for each school under Center level
I need to get a query result from a Derived Table that I have. Here is an example of the data from my Derived Table, based on AdvID. The following is a result of students that are in relation with the advisor.
studentPIDM AdvPIDM AdvID AdvFirstName AdvLastName Sprhold StvHlDesc 123456 14000 N12345678 John Smith 01 Letter Sent 123456 14000 N12345678 John Smith E1 Library 654321 14000 N12345678 John Smith NULL NULL 134567 14000 N12345678 John Smith 01 Letter Sent 134567 14000 N12345678 John Smith AM Admin Hold 134567 14000 N12345678 John Smith E1 Library 155544 14000 N12345678 John Smith NULL NULL 233555 14000 N12345678 John Smith NULL NULL
What I want, is to get the count of students that have holds, and the count of students that do not have a hold. From the data above, the students that do not have a hold have a NULL value on sprhold column. Also from this data, you can see that some students have multiple holds, but they should be counted as only once when the Count is performed. The desired result would look like the following:
SELECT ID_AnagraficaRivenditaFROM dbo.AnagraficaRivenditeWHERE EXISTS(SELECT *FROM dbo.Flussi_RivenditeWHERE dbo.Flussi_Rivendite.CodiceProdotto = 631 AND dbo.AnagraficaRivendite.ID_AnagraficaRivendita = dbo.Flussi_Rivendite.ID_AnagraficaRivendita)AND EXISTS(SELECT *FROM dbo.Flussi_RivenditeWHERE dbo.Flussi_Rivendite.CodiceProdotto = 615 AND dbo.AnagraficaRivendite.ID_AnagraficaRivendita = dbo.Flussi_Rivendite.ID_AnagraficaRivendita)GROUP BY ID_AnagraficaRivendita
hi, in this query (in which I extract all ID_AnagraficaRivendita who have a correspondence in table Flussi_Rivendite with CodiceProdotto = 631 AND CodiceProdotto = 615), I would like to receive also a count of extracted rows... have you any idea?? Thank you ;)
HelloI have this stored procedure:SELECT @openissue=ISNULL(COUNT(*),0) FROM TOpenIssue WHERE TOpenIssue .Code <> 'CLOSED' and Project=@project AND DateDIFF( day, TOpenIssue .DateStart, GETDATE() ) >= 0SELECT @oiclosed=ISNULL(COUNT(*),0) FROM TOpenIssue WHERE TOpenIssue .Code = 'CLOSED' and Project=@project SELECT @oipastdue=ISNULL(COUNT(*),0) FROM TOpenIssue WHERE TOpenIssue .Code <> 'CLOSED' and Project=@project AND TOpenIssue .DateEnd<getdate()Is there away to optimize it in only one select statement?Thanks
I have a problem, i have table which has a effective date, termination date and client, I have to have a count for active people month by month from july 2003 till may 2004. the date fields are in datetime format, how would i do that?
ok, so i have this table "SOLD_PRODUCTS" with this columns: idProduct (int), quantitySold (int)
so i want to know how many products i have sold.
i have this query
Code:
SELECT idProduct, COUNT(idProduct) AS Total FROM SOLD_PRODUCTS GROUP BY idProduct
and get this 74 5 75 2 79 1 etc etc etc
this works correctly, but it doesnt counts the quantity sold, so i changed the query to
Code:
SELECT idProduct, quantitySold, COUNT(idProduct) AS Total FROM SOLD_PRODUCTS GROUP BY idProduct
but it crashes, mssql says i must add quantitySold to the group by clause.
but if i put the query
Code:
SELECT idProduct, quantitySold, COUNT(idProduct) AS Total FROM SOLD_PRODUCTS GROUP BY idProduct,quantitySold
i dont get the expected data, the idProducts appear more than once, they are no longer grouped by, the results in the count(idProduct) are wrong (i am not sure what they are counting now)
so now i dont understand how i can make my query, all i want is a query where in one collumn i have the product id, and in the other the quantity sold (that would be the count of all the product ids found multiplied by the quantity sold)
so now i am completelly lost, now i dont know what to do with my query.
Hi, I'm working on a helpdesk project and I require the calculation of the holidays. I need to get the time difference of the assigned date and the solved date of the helpdesk tickets considering the week-end holidays and statutory holidays. Is there any possible way to do this. I need something similar to the NetworkDays function in excel. Thanks. Madhavi.
I have a table with following fields tdate custcode prodcode
table is filled with full year data and i want following result
I want count of distinct custcode in every past three months.
for example Result like this
month tjan tfeb tmar tapr tmay ..... tdec
prod1 prod2 . . prod5
And data under tmar should be count of distinct custcode of (jan,feb and mar) for corresponding prod code is required. Under tapr, count of distinct custcode of (feb,mar and apr) for corresponding prod code is required.
Can any1 help me please.
I am using MS SQL 2005 and above table is a big table (approx 10 million records)
Hi, I have two tables in my DB: tbl_Users: callSign(Char), FirstName(Char), LastName(Char) tbl_Events: CallSign(Char), TotalKM(Char), EventDate(SmallDateTime)... Plenty of others, but they're not relevnt.
The result that I want to see is:
CALL SIGN Last Name First Name Date Number Of Events TotalKM 111 MR. X 01/01/2008 3 40
I know this: The number of events is countable. if you do the following convert "Convert(int, TotalKM) you can sum up the Total KM.
I have a table which tracks changes by user, department and date. I want to construct a query which returns two numbers, the first is all the changes by department on a certain date. The second is all changes by user on the same date. I know that the queries seperately will look like,
SELECT COUNT(User) As NumberByUser FROM Table WHERE (Date = 'DateGoesHere') AND (User= 'UserGoesHere') GROUP BY User
SELECT COUNT(Department) As NumberByDepartment FROM Table WHERE (Date = 'DateGoesHere') AND (Department = 'DepartmentGoesHere') GROUP BY Department
But how do I go about linking the two in the same query? Any help would be appreciated, thanks!
Here's what I'm trying to do: 1. Output each country in one column 2. Output the number of subscriptions made from a member of that country where tblOrders.orderTypeID = 3 and tblSubscriptionPacks.TypeID = 1 in the next column 3. Output the number of subscriptions made from a member of that country where tblOrders.orderTypeID = 3 and tblSubscriptionPacks.TypeID = 2 in the next column
My problem was that I was doing joins, and I was somehow ending up with orders where the OrderTypeID was NOT equal to 3, even though I declared it specifically in the WHERE clause.
I am trouble getting the count of applications. In the below query MerApp.ApplicationID represents the applications. I'm trying to get a results of a count of applications for each sales team member.Before adding the COUNT() function. My query results show 3811 because it shows the sales team member numerous times because he has many applications with different IDs. For example sales team member A shows up in 75 rows because he has 75 different applications assigned to him.
SELECT (usr.FirstName + '' + usr.LastName)AS [Sales Team Member], MerApp.Assignedto, MerApp.ApplicationID, stm.UserID, MerApp.ActiveStatus FROM MerchantApplication MerApp INNER JOIN Users usr ON MerApp.assignedTo = usr.UserID INNER JOIN SalesTeamMembers stm ON usr.UserID = stm.UserID WHERE MerApp.ActiveStatus=1 GROUP BY MerApp.AssignedTo, usr.LastName, usr.FirstName, MerApp.ApplicationID, stm.UserID, MerApp.ActiveStatus
I want sales team member A to show up once with the count of applications to be 75 because that's how many are assigned to him.
This didn't work for me: SELECT (usr.FirstName + '' + usr.LastName)AS [Sales Team Member], MerApp.Assignedto, COUNT (MerApp.ApplicationID) AS [Applications], stm.UserID, MerApp.ActiveStatus FROM MerchantApplication MerApp INNER JOIN Users usr ON MerApp.assignedTo = usr.UserID INNER JOIN SalesTeamMembers stm ON usr.UserID = stm.UserID WHERE MerApp.ActiveStatus=1 GROUP BY MerApp.AssignedTo, usr.LastName, usr.FirstName, MerApp.ApplicationID, stm.UserID, MerApp.ActiveStatus
It didn't count the number of applications for each person.I have about 14 different sales team members. So I'm trying to have a query that produces 14 rows not 3811 rows
I have to write a query to get the count() of the customer who has max sales in the last 6 months.my query is
Select Inv_Cust,Count(Inv_Cust) as Salescount From Inv_Header Group By Inv_Cust,Inv_Date Having Inv_Date Between MIN(Inv_Date) And DATEADD(MM,6,min(Inv_Date)) which gives me a result like inv_cust ' Salescount