Problem With Group Totals And Counts
Sep 21, 2007
I am having trouble in SRS determining the distinct count and total for a very complex report.
Basically my dataset return 234 rows. In my report I am using a list (I have to use a list instead of a table for exporting reasons) and I am grouping by accountID.
If I do a CountDistinct(Fields!accountid.value) I still get 234. It's almost like it's not taking in the filter of the group.
I can do a RunningValue for each value and I see it count from 1-23. So I know that there are only 23 values being diplayed.
I also tried doing a CountDistinct(Fields!accountid.Value,"groupname") and I still get the total dataset.
Finally I do not want to do the counting on the dataset (meaning the in the query) because I want the flexibility to use filters for conditional reporting. (I have multiple scenarios in which I need to view the data).
View 8 Replies
ADVERTISEMENT
May 9, 2015
I have some data grouped in a table by a certain criteria, and for each group it is computed a subtotal for the group. Of the values from each of the group, I want to create a grand total on the report by adding every subtotal from each group.
Example:
...
....
Group1 Value
10
20
Sub Total 1: 30
Group2 Value
15
25
Sub Total 2: 40
Now, I would like to be able to add subtotal 1 (30) to subtotal 2 (40) and my grand total would be 70. Can I accomplish this task in SSRS?
View 5 Replies
View Related
Jun 6, 2014
I have the following:
SELECT '201305' AS PAYPERIOD,
EMPLOYEE,
RIGHT ('000' + CAST (DEPT_ID AS VARCHAR(3)) ,3) AS DEPARTMENT,
COUNT (EMPCODE) AS BONUSCOUNT_YTD
FROM Table1
WHERE (YEAR = 2013 AND PERIOD < 2)
GROUP BY EMPCODE, YEAR, PERIOD, DEPT_ID
[Code] ...
How can I get the counts to be cumulative? In other words, if an employee appears in pay period 201305 that's 1, if they then appear in pay period 201306 that becomes 2.
View 4 Replies
View Related
Nov 14, 2007
Hi,
Is there a way to display the sum of a group of a field?
I've created a group, but when I put the expression of SUM(Field) in the group footer, it gives me the total of Field for the whole dataset.
Is there a way I can display the just the Totals of the Groups?
so if my data looks like :
a | 1
a | 2
a | 3
b | 4
b | 5
b | 6
I want to display :
a | 1
a | 2
a | 3
Total a | 6
b | 4
b | 5
b | 6
Total b | 15
but instead, when i add the SUM expression into the footer group, I get :
a | 1
a | 2
a | 3
Total a | 21
b | 4
b | 5
b | 6
Total b | 21
View 8 Replies
View Related
Apr 10, 2008
I have been working on this 'Select Count' problem for the past week. Somehow, the Counts were multipled.2 requests showed 4 counts, 3 requests showed 9 counts, 4 requests showed 16 counts. The main question,to the best of my knowledge, is the coding for DBadapter.Fill method. After 'Left Join' 2 tables, how do I code the DataTable names? I have tried using TransDS.Tables("Table name") to solve the problem. Butthe counts still showed mulpile #.
Another question: why there are 1 '0' line and 2 'rollup' lines?
Examiner
Requested
Scheduled
Finished
noShow
Cancelled
Deleted
0
0
0
0
0
0
312
249
97
60
39
45
255
210
90
60
15
30
aaaa
4
4
0
0
0
0
bbbb
9
9
3
0
6
0
16
16
0
0
16
0
16
4
2
0
2
12
4
4
2
0
0
0
strApptsSQL = "SELECT c.LName + ', ' + c.FName AS Examiner, " & _
"COUNT(Case TransCode WHEN 'R' THEN 1 ELSE NULL END) AS Requested, " & _"COUNT(Case TransCode WHEN 'B' THEN 1 ELSE NULL END) AS Scheduled, " & _
"COUNT(Case TransCode WHEN 'F' THEN 1 ELSE NULL END) AS Finished, " & _"COUNT(Case TransCode WHEN 'W' THEN 1 ELSE NULL END) AS DSnoShow, " & _
"COUNT(Case TransCode WHEN 'D' THEN 1 ELSE NULL END) AS Deleted, " & _"COUNT(Case TransCode WHEN 'E' THEN 1 ELSE NULL END) AS Cancelled " & _
"FROM Sssss.dbo.TransHistory AS T " & _"LEFT JOIN Sssss.dbo.Requests AS r ON T.TransUserID = r.RequestStaffIDFK " & " " & _
"LEFT JOIN Employee.dbo.Contacts AS c ON r.RequestStaffIDFK = c.ContactPK " & " " & _"GROUP BY c.LName + ', ' + c.FName WITH ROLLUP " & _
"ORDER BY c.LName + ', ' + c.FName"
'declare and create the data adaptersDim oDAAppts As New OleDbDataAdapter(strApptsSQL, oConnect)
Try
' fill the Dataset using the data adaptersoDAAppts.Fill(TransDS, "Requests")
Catch oErr As Exception
' display error message in page
lblErr.Text &= oErr.Message & "<br />"
Finally
' be sure to close connection if error occurs
If oConnect.State <> ConnectionState.Closed Then
oConnect.Close()
End If
End Try
' bind DataGrid to tablegvExaminerAppts.DataSource = TransDS.Tables("Requests")
gvExaminerAppts.DataBind()
View 2 Replies
View Related
Mar 27, 2007
I have a report that groups by dept #, job code and earnings code.
9999 Administration
033 Secretary
200 Regular Pay 44.00 1000.00
300 Sick Pay 8.00 25.00
400 Overtime 3.00 75.00
8888 Janoitorial
055 Janitor
200 Regular Pay 24.00 800.00
300 Sick Pay 4.00 15.00
400 Overtime 1.00 45.00
On the last page of my report I want to sum the earnings totals by earnings number. For Example:
Totals
200 Regular Pay 68.00 1800.00
300 Sick Pay 12.00 40.00
400 Overtime 4.00 120.00
Can this be done?
View 5 Replies
View Related
Nov 30, 2007
I have a tabular report with grouping on the following fields:
Grouped rows: MDC, DrgDesc, ChronicOther
Detail row: Cases
The desired output follows:
MDC1
DrgDesc1
Chronic 50
Other 25
Total cases for DrgDesc1= 75
DrgDesc2
Chronic 20
Other 33
Total cases for DrgDesc2 = 53
etc....
I have everything working up to the Total cases for each DrgDesc. I tried adding a group footer to the Cases row, however this sub-totals for Chronic and Other rather than summing them together. Example is below.
MDC1
DrgDesc1
Chronic 50
50
Other 25
25
DrgDesc2
Chronic 20
20
Other 33
33
etc....
How can I achieve the desired result?
View 3 Replies
View Related
Aug 11, 2015
I have a table where I am grouping on one field and would like an individual (separate) count of values from another field of same table. So for example, I have following data:
instance_id, area, values
101 North 1
102 North 2
103 East 2
104 East 2
I would like to report on Area, and count of rows with different Values types, for example:
Area Value - 1, Value - 2, Value - 3
North 1 1 0
East 0 2 0
I am not sure what the technical term is for such report, but I can group by Area column, and but its aggregating counts on different Value types that I am having difficulty in performing in SSRS.
View 2 Replies
View Related
Aug 1, 2014
I'm trying using the GROUP BY CUBE aggregation. Currently I have this working as such:
SELECT
ISNULL(CONVERT(VARCHAR,Date), 'Grand Total') Date
,ISNULL([1 Attempt],0) [1 Attempt]
,ISNULL([2 Attempts],0) AS [2 Attempts]
,ISNULL([3 Attempts],0) AS [3 Attempts]
,ISNULL([4 Or More],0) AS [4 Or More]
[Code] .....
Basically this is used to work similar to a Pivot table in excel. My data will look as follows:
Date 1 Attempt2 Attempts3 Attempts4 Or MoreTotal
2012-09-04 239 68 2 8 317
The problem I'm having is the Total column. Although this is summing the line values correctly, the total should be based on the sum not count of attempts i.e. 1 x 239, 2 x 68, 3 x 2, 4 x 8
If I change the FROM select clause to use SUM instead of COUNT
SELECT
CONVERT(DATE,[Date]) Date
,ISNULL(AttemptsFlag,'Total') as Attempt
,SUM(NoOfTimes) AS Totals
FROM
XXXXX
GROUP BY
CUBE([Date],AttemptsFlag)
It will return the correct Total amount but not the right numbers for the Attempt groupings...
View 1 Replies
View Related
Sep 11, 2007
Hi, I have a matrix with 2 row groups and 1 column group.
CGroup1 Val1
CGroup1 Val2
Total
- RGroup1 Val1
RGroup2 Val1
In
In
Out
RGroup2 Val2
In
In
Out
- RGroup1 Val2
RGroup2 Val3
In
In
Out
RGroup2 Val4
In
In
Out
Total
Out
Out
Out
I want to change the row totals at the RGroup2 level. I have put an expression in the measure cell as:
=iif(InScope("matrix1_RowGroup2"), "In", "Out"). Shouldn't the values in the Totals Column on the far right evaluate to "In"? If not, how can I isolate the totals at the RGroup2 level?
Also, I found that when I put =fields!RGroup2.value in the expression for the cell, the Totals Column on the far right is blank but when I put =fields!RGroup1.value the correct value is properly displayed in the Total Column. Why does =fields!RGroup2.value not work?
View 7 Replies
View Related
May 18, 2015
I have a table with a row group "Sales Area" that lists customers per sales area. There is one column with the sales per customer and another column with the planned sales per customer.A third column "Under Plan" is a simple calculation that compares the two Report Items of the sales to the plan and puts a 1 there if plan is higher. My issue is how to get the total of the group "Sales Area", to display the group total of all customers that are under plan. SSRS doesn't let me use aggregate functions on group totals;Unfortunately I cannot pre-calculate the "Under Plan" figure in the query, since this example is a simplified overview (the customers is a distinct count for example...)
View 5 Replies
View Related
Oct 27, 2015
I have a field on my report that uses the following expression to determine the commission amount for each order line. It works correctly to get the commission amount for each line, however, I need to get a total of the commission amount for each Salesperson.
My report is grouped in the following manner:
Salesperson, Type of Sale, Invoice Number, then the detail invoice line items (where the formula below reside). How can I get the totals for the Salesperson and the Type of Sale?
=IIF(Fields!PartIsSerialized.Value=True, (Sum(Fields!OrderLineSubtotal.Value)/Fields!Quantity.Value),Sum(Fields!OrderLineSubtotal.Value))
*
IIF(
Fields!TransactionType.Value Like "*USED*", (Parameters!CommissionRateUsed.Value*.01),
[Code] ....
View 2 Replies
View Related
Dec 19, 2007
Hello Everyone
I've created a report with a simple dataset that is similar to this
City , RequestID, Request Amount, ClaimID, ClaimAmount
El Monte 791 52,982.00 2157 41,143.75
El Monte 3691 11,838.00 3140 8,231.14
El Monte 3691 11,838.00 3141 990.00
El Monte 3691 11,838.00 3142 2,615.00
So I group by City, RequestID. On the first group I specified the expression to be City and in the header I list the city and in the footer I list the sum of Request amount. On the second group I specified the group by Request so in the header I placed requestID and on the footer I placed Request Amount. I set request information to hide the duplicates and I even add =Sum(Fields!RequestApprovedGrandTotal.Value,"GroupByRequestID") the scope of the group. But this is what I get:
For requestID = 3691 for Request Amount is 35,514.00 not 11,838.00. All the claim sums are correct and they are located on the detail row.
I've read that a work around is to create multiple dataset but I honestly believe that something as simple as this should work on the reporting server 2000. So I've come to the conclusion that I must be doing something wrong. Can someone give me a hand on this. Thanks.
View 6 Replies
View Related
Oct 28, 2015
I have a report builder drill down report. I have row groups with totals. It looks like the attached. The problem is when the report is not expanded the Grand Totals column is not accurate... it is displaying the totals of one of the rows when expanded.The expression in the Total Show text box is
= Switch (
MID(Fields!protocol_id.Value,1,7)="THERAPY",
Sum(IIF(Fields!status.Value = "CO", CDbl(Fields!TX_CO.Value), Nothing)),
MID(Fields!protocol_id.Value,1,7) = "GENERAL" and MID(Fields!program_id.Value,1,6)
= "INTAKE", Sum(IIF(Fields!status.Value = "CO",
[code]...
Is there any way to not display the expression in the Total columns unless the report is expanded?
View 2 Replies
View Related
Oct 2, 2006
I have been providing sales data for a few months now from a table that is set up like this:
Date WorkDay GasSales EquipmentSales
9/1/2006 1 100.00 200.00
9/4/2006 2 50.00 45.00
etc.
As can be seen, the data is daily, i.e., on the first workday of September we sold one hundred dollars in gas and two hundred dollars in equipment. On the second workday of September we sold fifty dollars in gas and forty-five dollars in equipment.
Now, however, the data I have to pull from is cumulative. So, using the last table as an example it would look like this:
Date_WorkDay_GasSales_EquipmentSales
9/1/2006 1 100.00 200.00
9/4/2006 2 150.00 245.00
etc.
To make things more complicated, the powers that be wanted this data presented in this fashion:
Total Sales:
1_2_etc.
300.00 95.00 etc.
So, I have been doing a pivot on a CRT to get the data to look like I want. The code is like this:
with SalesCTE (Month, WorkDay, [Total Sales])
as
(
SELECT
datename(month, cag.date),
cag.WorkDay AS [Work Day],
sum(cag.sales_gas + cag.sales_hgs) AS [Total Sales]
FROM CAG INNER JOIN
Branch ON CAG.[Oracle Branch] = Branch.OracleBranch
group by cag.date, cag.WorkDay
)
select * from SalesCTE
pivot
(
sum([Total Sales])
for WorkDay
in ([1],[2],[3],[4],[5],,[7],,[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23])
) as p
So, my question is:
How do I get the data to give back daily totals instead of the cumulative amounts for each workday? If the query was a simple one, I'd do something like
select [1] as [Day 1], [2]-[1] as [Day 2], [3]-[2] as [Day 3], etc.
but the query is far from normal, with the CRT and the pivot. I can't seem to get it to work how I'd like.
Any advice/answers? Thanks in advance!!!
P.S. I don't know how to get it to quit with the freakin' smileys.... I suppose you can figure out what my code is really supposed to look like above. Needless to say, it doesn't include a devil face and a damn music note...
View 12 Replies
View Related
Sep 29, 2015
I have an SSRS 2012 table report with groups; each group is broken ie. one group for one page, and there are multiple groups in multiple pages.
'GroupName' column has multiple values - X,Y,Z,......
I need to group 'GroupName' with X,Y,Z,..... ie value X in page 1,value Y in page 2, value Z in page 3...
Now, I need to display another column (ABC) in this table report (outside the group column 'GroupName'); this outside column itself is another column header (not a group header) in the table (report) and it derives its name partly from the 'GroupName' values:
Example:
Value X for GroupName in page 1 will mean, in page 1, column Name of ABC column must be ABC-X Value Y for GroupName in page 2 will mean, in page 2, column Name of ABC column must be ABC-Y Value Z for GroupName in page 3 will mean, in page 3, column Name of
ABC column must be ABC-Z
ie the column name of ABC (Clm ABC) must be dynamic as per the GroupName values (X,Y,Z....)
Page1:
GroupName Clm ABC-X
X
Page2:
GroupName Clm ABC-Y
Y
Page3:
GroupName Clm ABC-Z
Z
I have been able to use First(ReportItems!GroupName.Value) in the Page Header to get GroupNames displayed in each page; I get X in page 1, Y in page 2, Z in page 3.....
However, when I use ReportItems (that refers to a group name) in the Report Body outside the group,
I get the following error:
Report item expressions can only refer to other report items within the same grouping scope or a containing grouping scope
I need to get the X, Y, Z ... in each page for the column ABC.
I have been able to use this - First(Fields!GroupName.Value); however, I get ABC-X, ABC-X, ABC-X in each of the pages for the ABC column, instead of ABC-X in page 1, ABC-Y in page 2, ABC-Z in page 3, ...
View 4 Replies
View Related
Feb 5, 2007
If I right click and browse the properties for the table I can get the value of rows. But for the same table if I do select count(*) from table the value does not match the table properties rows. Please can some one tell me why this is so?
SQL Newbie
View 2 Replies
View Related
Jan 8, 2007
I expect to get a record below with a count of 0 (and I do), but when I take the comments out (--) of lines 1 & 6 I don't understand why I get no records at all. I need to be able to see all teams in EvalAnswers even if none of the records satisfies the where clause.1 select Count(*) as cnt--, TeamID
2 from EvalAnswers
3 where CoID=@CoID
4 and EvaluatorID=@EvaluatorID
5 and (Scr0=0 and Sugg0 is NULL)
6 --group by TeamID
7
View 4 Replies
View Related
Sep 6, 2007
I need to create a view that shows the number of times that clients made payments, how many clients, and how much they paid over a period of time. I'm not sure if I can use a case for this. How can I put something like this together?
I have a tblClients with a clientid field
I have a tblPayments with the clientid, pmtdate, and pmtamount
For example:
1 Payment ----- 23 Clients ----- $16000
2 Payments ----- 12 Clients ----- $32000
3 Payments ----- 4 Clients ----- $13000
etc...
View 3 Replies
View Related
Nov 15, 2000
I need a procedure that will go to a database and give me all the row counts for the user tables.
Does anyone know how I can get this?
Thanks,
Dianne
View 3 Replies
View Related
Apr 20, 2004
Hi,
...giving a very 'summarized' scenario of the problem I have trying to
solve all day (make it 2 days now).
Below are the relevant DDLs... I am not listing the DDLs of my other tables:
CREATE TABLE [SalesFACT] (
[varchar] (10),
[TransDate] [varchar] (10),
[SaleAmt] [float],
[CustCode] [varchar] (10)
. . .
)
I populate the above table via a DTS and have checked and have verified that correct data is coming in... I also have a product master table; for business reasons we can have the same product created with different ProductCodes though the rest of the Product details are EXACTLY the same. We have covered this using a field named 'UniqueProdCode'.
CREATE TABLE ProdMaster(
[ProdCode] [varchar] (10),
[ProdName] [varchar] (35),
[UniqueProdCode] [varchar] (10),
... many other product fields e.g. unit price, category etc...
...
)
First a small Request:
Please note that I have NOT defined links between my tables (in the diagram editor) nor have I defined Primary keys (or any constraint) for any of the tables. When you kindly reply, please suggest I should define primary keys for the tables and also link them in the diagram editor.
[u]THE PROBLEM:
When I do a count(*) query on the table 'SalesFACT', I get the correct number of records.
If I create a view, add table 'SalesFACT' and table ProdMaster, link the
UniqueProdCode field of table 'SalesFACT' with the UniqueProdCode field of ProdMaster (so that I can also get the name, category, etc. for the products in the SalesFACT), and run a count(*) query I get a much higher and incorrect number of rows. The SQL for the view is:
SELECT dbo.SalesFACT.TransDate, dbo.SalesFACT.UniqueProdCode,
dbo.SalesFACT.SaleAmt
FROM dbo.SalesFACT INNER JOIN dbo.ProdMaster ON dbo.SalesFACT.UniqueProdCode = dbo.ProdMaster.UniqueProdCode
Kindly note that I have checked and the contents of the table SalesFACT' UniqueProdCode field DOES contain the correct data i.e. it contains the UniqueProdCode and NOT the ProdCode.
But if i link the "wrong fields", I get the correct count count :confused: i.e. I create a very similar view (as mentioned above) but instead link the UniqueProdCode of table SalesFACT with the ProdCode field (not the UniqueProdCode field) of ProdMaster
table I get the correct count. This is really driving me nuts and I just can't understand what's going on and why the "REVERSE" logic. For your convenience here is the SQL for the 2nd view:
SELECTdbo.SalesFACT.TransDate, dbo.SalesFACT.UniqueProdCode,
dbo.SalesFACT.SaleAmt
FROM dbo.SalesFACT INNER JOIN dbo.ProdMaster ON dbo.SalesFACT.UniqueProdCode = dbo.ProdMaster.ProdCode
Please guide... I have run out of all the things that I could check and thus this SOS and F1
Billions of thansk in advance.
View 2 Replies
View Related
Dec 12, 2006
I just inherited an app, where I have two tables that look like this:
[Owners]
--------
Owner
...
[Cases]
-------
Owner
Status
Assigned
...
I need a query to get results that look like this:
[Results]
--------
Owner
# of cases records where Status='Open'
# of cases records Where Status='Pending'
# of cases records WHERE Status<>'Closed' AND Assigned=''
I have one query that works already, but it's using several nested selects. I know I ought to be able to do this using group by instead, and I like to know how.
View 4 Replies
View Related
Jan 24, 2006
I am creating a database for a soccer league.
I would like to write a query that would give me results in a league table form .
How could I combine different count queries such as:
select hometeam, count(*) as homegames from matches where comp="en1pp" group by hometeam
order by hometeam
select hometeam, count(*) as homewins from matches where homescore>awayscore group by hometeam order by hometeam
into one query giving a three column result homteams, homegames, homewins.
Thanks
View 6 Replies
View Related
Sep 19, 2006
Hello :)
I seem to have somehow got myself into a situation where I'm having to run the following SELECTs, one after another, on a single ASP page. This is not tidy. How can I join them all together so I get a single recordset returned with all my stats in different columns?
SELECT COUNT(*) FROM tblQuiz WHERE [q3] = '5 years +' OR [q3] = '2 - 4 years'
SELECT COUNT(*) FROM tblQuiz WHERE [q4] <> '' AND [q4] IS NOT NULL
SELECT COUNT(*) FROM tblQuiz WHERE [q5] = 'Unhappy'
SELECT COUNT(*) FROM tblQuiz WHERE [q6] = 'Yes'
SELECT COUNT(*) FROM tblQuiz WHERE [q7] = 'Yes'
SELECT COUNT(*) FROM tblQuiz WHERE [q8] <> '' AND [q8] IS NOT NULL
View 8 Replies
View Related
Oct 6, 2006
Hi all,
I have a need to compare the number of rows returned from table A when it is joined to table B to the number of rows returned when there are no joins involved. If the number of rows returned are the same, then I need to proceed to execute my next step else end.
So, If RowCount A = RowCount A when A joined to B
THEN Goto Next Step
Else End
I need to put the above logic in a sp that I want to execute using a job.
Help is appreciated.
V
View 5 Replies
View Related
Apr 22, 2004
OK I have a query that bring back a unique identifier and text value
for instance
Select Distinct global_id, Page_url from PageList
global_id page_url
---------- ---------------------------------
4306549 /true/attitudes.htm
1460753 /true/current/answerthis.htm
4303667 /true/current/answerthis.htm
4306549 /true/current/answerthis.htm
Now I want to do a count of the page_url.
Can this be done in one SQL statement using a sub query of some kind
View 6 Replies
View Related
Nov 28, 2007
Hi I have this
select emailid,
count(emailid) as 'No.of occurences',
FROM tableA
where start_moment between '2007-11-01' and '2007-11-02'
GROUP BY emailid
having (COUNT(emailid) > 1)
Fair enough this returns the emailid along with the amount of times it appears (all greater then 1 .. duplicated in other words)
My question is , there is also a start_moment field in tableA
so i need to get the max or min start_moment along with the above result?
View 3 Replies
View Related
Dec 4, 2007
I have members in a database who have paid thru dates. I am creating retention reports
I created a cross tab in Crystal (using SQL) that counts records that paid within a certain year. I need to create a script that will let me find when members skip payment for a year. Any ideas?
I was thinking of running a count of all paid (Activity) records, but still kind of stuck.
DZ
View 9 Replies
View Related
Jul 20, 2005
I want to get a resultset of every table in the database, with thecurrent record count of each. What is the easiest way to do this?I can get the list of tables with:Select s.name from sysobjects s where xtype = 'U'each s.name is a table name, but I'm not sure how to join a record countcolumn to the resultset.Thanks,RickN
View 4 Replies
View Related
Sep 19, 2007
I'm sorry if someone has already posted this but I've looked through a few pages to see if someone had already posted and I couldn't find anything. Anyways, I have two counts and I would like to divide them to get a percentage. Basically I would like to see a percentage of how many tickets are overdue. Here's my SQL:
select count(*)[No. of Tickets Overdue],
case
when sum(datepart(dd,getdate() - j.report_date))>= pt.due_hours then 'Over Due'
when sum(datepart(dd,getdate() - j.report_date))>= pt.due_hours then 'Over Due'
when sum(datepart(dd,getdate() - j.report_date))>= pt.due_hours then 'Over Due'
when sum(datepart(dd,getdate() - j.report_date))>= pt.due_hours then 'Over Due'
end [Ticket Status]
from whd.priority_type pt
inner join whd.job_ticket j on pt.priority_type_id = j.priority_type_id
where j.status_type_id = '1' and j.deleted = '0' and not j.priority_type_id = '5' and not j.priority_type_id = '6'
group by pt.due_hours
order by pt.due_hours desc
COMPUTE SUM(count(*))
select count(*)[Count2]
from whd.job_ticket jt
where jt.status_type_id = '1' and jt.deleted = '0'-- and not jt.priority_type_id = '5' and not jt.priority_type_id = '6'
--COMPUTE [No. of Tickets Overdue]/[Count2]
I know this isn't correct but basically the commented line at the bottom is what I want to do. I've only been doing SQL statements for a few months now, so I know its novice but any help is appreciated.
Thanks in advance.
View 7 Replies
View Related
Oct 8, 2006
Hi,
I have 10 databases created. each data base has arround 100 tables . i need to keep track of the number of records in each table of 10 databases and the last modification date on that table. The solution should be programatically by running a T-sql program or any stored procedures or any other but mechinical.
the output should specify the following:
Table #number_of_recs Last_date
database_name.Table_name ###,###,### DD/MM/YY
I will appreciate any assistance in this regard.
thanks,
View 1 Replies
View Related
May 20, 2008
I have a table that is linked to other tables in one to many relationship.I have a query using LEFT OUTER JOINs to join the tables together.There are multiple counts in the query and count numbers are messed up.(if there are 4 records from one table and 3 from the other - it shows 12 as the count) Your help is appreciated.
View 2 Replies
View Related
Jan 26, 2004
How would I create a sql statement with 3 or 4 counts which would represent 3 or 4 different columns in a datagrid?
For example
SQL = "SELECT Count(department_id) as "totals1" FROM nonconformance WHERE department_id = '1'"
How would I make additional counts in this SQL statement that looks for when department_id=2 and 3 etc....
Thanks
View 7 Replies
View Related
