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
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.
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
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 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?
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 ??
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'
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.
Hi, i created a cube that has 2 measures. I created the measures by selecting the columns from my fact table, but the function that applied in the measures was the sum function. I need to apply the count function in my measure. How can i do that?
I have data like this in a two column temporary table -ID Age23586 323586 323586 223586 223586 123586 123586 123586 123586 1I need to create a temporary table that look like this:ID Age1 Age2 Age3 Age423586 5 2 2 0However, what I get is this:23586 5 NULL NULL NULL23586 NULL 2 NULL NULL23586 NULL NULL 2 NULLHere is the query that I am using...select managed_object_id, (select count(Age) where Age = 1) As Age1,(select count(Age) where Age = 2) as Age2,(select count(Age) where Age = 3) as Age3,(select count(Age) where Age = 4) as Age4into #enhancementCount from #enhancementsgroup by managed_object_id, AgeWhere's my mistake?Thanks-Danielle
I have a database that contains a column for UnitName , BeginDate andEndDate.I want to pass two parameters (@BeginDate and @EndDate) and retrieve atable of valuesthat include UnitName along with Counts for each UnitName.SELECT UnitName, COUNT(BeginDate) AS Start(SELECT COUNT(EndDate) AS Finish WHERE EndDate BETWEEN @BeginDate AND@EndDate)FROM TableWHERE BeginDate BETWEEN @BeginDate AND @EndDateGROUP BY UnitNameORDER BY UnitNameThis works. But when I try to add another count by using a subselect Iget an error dealing with GROUP BY not including the column in mysubselect.How is the best way to Count two columns using Group By.
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.
This is so complicated (for me) because I usually only work with single table and simple queries (SELECT, INSERT, UPDATE), but now I am in a situation where I am stuck.
What I am trying to archive is that: when a project manager logged-into his/her account, a grid-view will show a quick overview for all of his/her projects (id, created date, name and how many files are in pending) like below picture:
3 tables will be involved are:
Sample data for manager_id = 11
I tried this query but it not worked, it seems to display all columns right but the COUNT pending files column (assume the manager_id = 11)
SELECT COUNT(file_id) as 'Pending files', projects.project_id, projects.project_name, projects.status, projects.start_date FROM ((project_manager INNER JOIN files ON project_manager.mag_id = files.manager_id AND project_manager.mag_id = 11 AND file_status = 'Pending') INNER JOIN projects ON projects.project_id = project_manager.project_id) GROUP BY projects.project_id, projects.project_name, projects.status, projects.start_date ORDER BY projects.status, projects.start_date DESC
The result is accurate but the query execution time is 3-4 minutes for 10 fact records, when i use multiple dimension. it is showing me 0 valus for this measure for all the members for the dimesion attribute which doen't have any customer order. example it shows all the member of date dimension. is there any way to reduce the rows. i think this is the reason to take more execution time.when i use EXCCLUDEEMPTY the result is NULL
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.
I am selecting the count of the students in a class by suing select COUNT(studentid) as StCount FROM dbo.student But I need to use a case statement on this like if count is less than 10 I need to return 'Small class' if the count is between 10 to 50 then I need to return 'Medium class' and if the count is more than 50 then 'Big class'.
Right now I am achieving this by the following case statement
SELECT 'ClassSize' = CASE WHEN Stcount<10 THEN 'Small Class' WHEN Stcount>=10 and StCount<=50THEN 'Medium Class' WHEN Stcount>50 THEN 'Big Class' END FROM( select COUNT(studentid) as Stcount FROM dbo.student) Stdtbl
COL1 | COL2 | COL3 | COL4 1 | FD | DR. A | Y 2 | FD | DR. A | Y 3 | FD | DR. A | N 4 | FD | DR. A | Y 5 | FD | DR. A | Y 6 | PF | DR. A | Y 7 | FD | DR. B | Y 8 | PF | DR. B | N
Consider the script below:
SELECT COL2, COL3, COUNT(COL1) AS TOTALS FROM CASES GROUP BY COL2, COL3 ORDER BY COL3, COL2
The script above produces the following output:
COL2 | COL3 | TOTALS FD | DR. A | 5 PF | DR. A | 1 FD | DR. B | 1 PF | DR. B | 1
I need to add one more column to the script that counts records with 'Y' in COL4 for each COL1 category (FD, PF). The final dataset would look like this:
COL2 | COL3 | TOTALS | NEWCOL FD | DR. A | 5 | 4 PF | DR. A | 1 | 1 FD | DR. B | 1 | 1 PF | DR. B | 1 | 0
I am having a hard time trying to use COUNT() on multiple columns with the GROUP BY restrictions that exist.
I have created a report which has 3 groups. The report output as shown below. I am having trouble getting the SUM of Total Credtis for each Org.
Can't seem to get the total 42 and 16 (highlighted), but can get total unists 11 and 13. I get expression contains aggregate function. This is because Units assessed is the Count of IDs (details hidden from the report).
Report has three groups Org , Assessor and Unit.
Can someone please help me with this?
Appreciate help.
Thank you,
Ski
Org 1(Group1)
Unit Credits Units Assessed(# of Trainees) TotalCredits
I am working on a report using SQL server Business Intelligence development studio in which I need to count the total number of saleslines that either has a status : Delivered or Invoiced.
I am using the inbuilt count function under the calcuations, as: Count(Fields!SalesStatus.Value, scope). I am not sure what scope means here however, I read that scope refers to some dataset, dataregion or grouping. I dont know what that means. I shall be really thankful if someone can help me with this.
I'm trying to get a calculation based on count(*) to format as a decimal value or percentage.
I keep getting 0s for the solution_rejected_percent column. How can I format this like 0.50 (for 50%)?
select mi.id, count(*) as cnt, count(*) + 1 as cntplusone, cast(count(*) / (count(*) + 1) as numeric(10,2)) as solution_rejected_percent from metric_instance mi INNER JOIN incident i on i.number = mi.id WHERE mi.definition = 'Solution Rejected' AND i.state = 'Closed' group by mi.id
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 use SQL 2000 I have a Column named Bool , the value in this Column is 0�0�1�1�1 I no I can use Count() to count this column ,the result would be "5" but what I need is "2" and "3" and then I will show "2" and "3" in my DataGrid as the True is 2 and False is 3 the Query will have some limited by a Where Query.. but first i need to know .. how to have 2 result count could it be done by Count()? please help. thank you very much
SQL 2000I have a table with 5,100,000 rows.The table has three indices.The PK is a clustered index and has 5,000,000 rows - no otherconstraints.The second index has a unique constraint and has 4,950,000 rows.The third index has no constraints and has 4,950,000 rows.Why the row count difference ?Thanks,Me.
