Counting Parent Records And Displaying Total??
Feb 5, 2004
We have an helpdesk sytem on SQL 2000. I am trying to show how much calls have been assigned to each parent category plus it's child categories in a single row. The thing is when I run my query it display parent and child categories and each on it's own rows. I do get the call totals for each row, but I would like to add the totals of the rows together and display it on row. The new table must have then 6 rows (because there is 6 parent categories) with the total of all calls for that parent category, as well as it's childs. Example:
parameter # Calls
------------ --------
desktoppp 5
desktopqq 6 {This is what I am getting at this stage}
desktop t 4
network
r 9
networkgg 10
softwarevv 3
This is what I would like to have:
parameter # Calls
------------ -------
desktop 15
network 19
software 3
Please Help!
View 2 Replies
ADVERTISEMENT
Jun 8, 2007
I need to add up the number of people who joined this month and compare that number to the number of people who joined last month and display the results in a report:
FirstName..LastName.....StartedDate
Randy......Simpson......5/4/2007 10:00:00 PM
Steve......Rowe.........5/2/2007 10:00:00 PM
Eric.......Dickerson....5/4/2007 10:00:00 PM
Gloria.....Sanches......5/1/2007 12:00:29 AM
Andres.....Marcelino....5/1/2007 12:06:31 AM
katie......ryan.........6/4/2007 12:08:35 AM
Denise.....River........6/4/2007 12:27:14 AM
Kellog.....Stover.......6/5/2007 12:37:20 AM
Glenn......Sanders......6/1/2007 12:42:40 AM
View 15 Replies
View Related
Apr 3, 2008
Lets suppose I have a db which looks like:District FundVAE AVAW AVAE BDC CDC ANYS BMD CVAW AI would like a query which displays the following dynamically. I don't know each unique member of the Fund field, they could be added by the end user. It might include M,J,S etc. I need the columns dynamically created. I can do it statically if I know the distinct members of fund. I would like to be able to do it dynamically. Any ideas? The static version with multiple queries is at the end.District Total A B CVAE 2 1 1 VAW 2 2 DC 3 2 1 NYS 1 1 MD 1 1
Query Name =GroupByDistSELECT district, COUNT(district) AS dcountFROM dbo.mydbGROUP BY districtQuery Name=DCountASELECT district, COUNT(district) AS dcountFROM dbo.mydbWHERE (fund = N'A')GROUP BY district
Query Name=DCountBSELECT district, COUNT(district) AS dcountFROM dbo.mydbWHERE (fund = N'B')GROUP BY district
Query Name=DCountCSELECT district, COUNT(district) AS dcountFROM dbo.mydbWHERE (fund = N'C')GROUP BY district
SELECT GroupByDist.district, GroupByDist.dcount AS Total, DCountA.dcount AS A, DCountB.dcount AS B, DCountC.dcount AS C From GroupByDist LEFT OUTER JOIN DCountC ON GroupByDist.district = DCountC.district LEFT OUTER JOIN DCountB ON GroupByDist.district = DCountB.district LEFT OUTER JOIN DCountA ON GroupByDist.district = DCountA.district
View 8 Replies
View Related
Jun 10, 2006
Hi All
I have:
select prod_id , count(prod_id)[Number of Times]
from orders_tbl
group by prod_id
order by 1;
I get a result however, how do sum up the total number of times ?
Thanks
View 5 Replies
View Related
Oct 20, 2007
hi,
i have a stored procedure SELECT UserName AS Visitor, COUNT(VisitID) AS TotalVisit
FROM UserVisits
WHERE (ProductID = @ProductID) AND (AnonimIP IS NULL)
GROUP BY UserName
UNION
SELECT AnonimIP AS Visitor, COUNT(VisitID) AS TotalVisit
FROM UserVisits AS UserVisits_1
WHERE (ProductID = @ProductID) AND (UserName IS NULL)
GROUP BY AnonimIP
this will return something like:
zuperboy90 - 4 visits
ANONIMOUS - 6 visits
85.104.103 - 2 visits etc
how can i count the rows returned in both selections (4+6+2 = 12) ?
thank you
View 9 Replies
View Related
Aug 25, 2007
Hi everyone,Does exist an easy way to count the actually number of queries executed within a page?I've searched here and in google but found anything...Thanks in advance!
View 3 Replies
View Related
Nov 27, 2007
I am working on an ASP.NET site to display information about authors and books. I'm working on the Author page right now and I'm having trouble getting it to display the data I want.
I'm trying to do everything with declarative controls and as little code-behind as possible.
I have a datasource on the main page that selects the Author data and provides this to a FormView. Inside the FormView is another datasource that takes the AuthorID as a parameter and selects the list of books, which is provided to a DataList.
Inside the DataList I display some limited Book information. I also want to display some child items of the Book (grandchildren of the author), for example other authors who contributed to that book, or alternative titles. I have tried using datasources in each DataList row with a parameter set to the BookID, and it works just great, but the response time is just not acceptable. Each datasource of possible several dozen is making its own call to the database and it's just too slow.
If I use code I can grab all the data in one operation and use relations in the DataBinding event (I believe) to select the data I want, but this is a bit cumbersome and I expect will cause trouble if I want to eventually use an ObjectDataSource.
Incidentally, on a display-only page I use the XMLDataSource and it works great, but I need read-write access on the Edit page.
Is there a good way to do what I'm trying to do?
Thanks,
Graham
View 3 Replies
View Related
Apr 16, 2015
I have a parent Child attribute in my dimension. It show the correct ID value in the reports but when I change the value from the NameColumn to be something else it still only shows the ID value. I have an identical case in another dimension and it is working correctly. However this dimension for some reason does not seem to work and I believe they are built the same way I must be missing something. I have tried to show the OrgNodeID and tried to display the OrgNodename but neither displays all it displays is the intdimParentOrgNodeID.My dimension table looks something like this
intdimOrgNodeID int Key (surreget key)
intOrgNodeID int (Actual ID)
intDimParentOrgNodeID
intOrgNodeName
[code]....
View 4 Replies
View Related
Sep 2, 2015
My query below displays a total row, but it displays above the city, I want it to display below the city. But now their is an additional caveat, I have a table that sets the order each city & total row should display in. How can that be added to my query to set the display order?This is what I have that displays the total above the city, could this be ammended to join in the order table and display in that order? SQL Server 2008
Create Table studentdata
(
homecity varchar(100),
usernumber int,
userstatus varchar(100),
usernumbermanager varchar(100)
[code]....
View 8 Replies
View Related
May 15, 2014
Displaying a list of parent and child objects in a certain way, here's the code to create and populate an example dataset.
CREATE TABLE OrgStructure
(orgunitID int,
OrgUnitName varchar (100),
ParentID int)INSERT INTO OrgStructure
VALUES
(1,'OrgUnit 1', 0), (2, 'OrgUnit 2', 0), (3, 'OrgUnit 3', 0),
[Code] ....
The 3 OrgUnits with no parentID in one column (parentOU), then any OrgUnits who's parentID matches the orgunitid of the 3 OrgUnits in ParentOU in the next column (Child OU1), followed by any OrgUnits who's parentID matched the orgunitID of the OrgUnits in 'Child OU1' would be in the third column (Child OU2)
The dataset I'm working on contains ~700 rows and the OrgUnitNames can be anything, they aren't labelled like the example dataset with 1.1.1 and so on.
View 2 Replies
View Related
Sep 7, 2015
declare @table table (
ParentID INT,
ChildID INT,
Value float
)
INSERT INTO @table
SELECT 1,1,1.2
[code]....
This case ParentID - Child 1 ,1 & 2,2 and 3,3 records are called as parent where as null , 1 is child whoose parent is 1 similarly null,2 records are child whoose parent is 2 , .....
Now my requirement is to display parent records with value ascending and display next child records to the corresponding parent and parent records are sorted ascending
--Final output should be
PatentID ChildID VALUE
33 1.12
null3 56.7
null3 43.6
11 1.2
null1 4.8
null1 4.6
22 1.8
null1 1.4
View 2 Replies
View Related
Jun 20, 2007
Hi,
I have requirement to display Total number of Rows in a Report in Page Header.
I have written the following code in Page header it shows RowCount for the Page only.
=Count(ReportItems!textboxInTableCell.Value)
Can anyone please help on this?
Regards
Raghav
View 3 Replies
View Related
Aug 18, 2006
Hello all.
I am looking to select all from a particular row where one field occurs more than once. The code i'm, trying isnt working:
select * from U_DATAFILE
where count(group by VIEW_ACTION)>1
Thanks all :)
View 10 Replies
View Related
May 29, 2007
I have a table with three fileds
1 - SmsTime (datatype = datetime)
2 - MsgCnt (datatype = int)
3 - Message (datatype = varchar)
Here MsgCnt means it will give the number of sms part (70 charector is equal to One SMS).
So in each row MsgCnt may be vary. Some time it may be 1 or 3 or 5 or 2 etc.
Now here I need to count how many MsgCnt is received on Today. Its not to count the rows but counting the MsgCnt fild value.
Is there any sql way to find it.
Plesase help me!
View 8 Replies
View Related
Feb 26, 2001
I report SQL Server table information (table names, field names, field datatypes, etc.) to my users using an ACCESS front end. Most of this information exists in the system tables. But I can't find a record count per table in the system tables. What's the fastest way to get a record count for all tables?
View 1 Replies
View Related
Sep 14, 2006
I was wondering if i would be able to add a column in a view that assigns a value to each record and incriments the number each time by 1. Like the way an identity field works in a table.
Is this possible using a view?
View 5 Replies
View Related
May 27, 2015
I have a table that contains a list of departments (about 50) and another table that contains helpdesk tickets, each record storing the ID of the department sending the ticket. So I'm trying to count how manay tickets per department, I tought of using a subquery and a WHILE cycle but Its just not happening..I sketch up this query:
Code:
WHILE (SELECT MAX(DepartmentID) AS c FROM dbo.tblDepartment) i < i.c
BEGIN
SELECT COUNT(DepartmentID) AS DepartmentCount
FROM dbo.tblTask
WHERE (DepartmentID = @Variable)
END
how could I build this query or what better way of doing the job there are...
View 1 Replies
View Related
Nov 30, 2007
Hi,I am struggling with a simple query, but I just don't see it.I have the following example table.Table MessagesID Subject Reply_to1 A 02 Ax 13 A 14 B 05 By 46 C 0The table holds new messages as well as replies to messages.Messages with Reply_to = 0 are top messages, the other messages arereplies to a top message. The subject of a reply message does notnecessarily have to be the same as the subject of the top message.What I would like to have returned is this: a list of messages whereReply_to = 0 and the number of replies to this message.ID Subject Num_replies_to1 A 24 B 16 C 0Any assistance would be greatly appreciated.
View 3 Replies
View Related
Jul 20, 2005
I seem to remember reading many moons ago about a function where youcan retrieve a count of the last recordset you opened.For example:I've got a stored procedure that returns a recordset using the TOP 10so I only get the top 10 records. I need to know the recordcount but Idont want to reuse the SELECT statement because its quite complex.Any ideas?What does @@Count do?Thanks in advance
View 1 Replies
View Related
Jun 9, 2007
Is there an easy way to count all records in a database?
View 3 Replies
View Related
Aug 11, 2015
Table1 contains fields Groupid, UserName,Category, Dimension
Table2 contains fields Group, Name,Category, Dimension (Group and Name are not in Table1)
So basically I need to read the records in Table1 using Groupid and each time there is a Groupid then select records from Table2 where Table2.Category in (Select Catergory from Table1)
and Table2.Dimension in (Select Dimension from Table1)
In Table1 There might be 10 Groupid records all of which are different.
View 9 Replies
View Related
Jul 14, 2004
I am trying to count records in my stored procedure. Can someone please help me.
these are the two procedures I am using
Alter Procedure usp_rptQualityReport As
SELECT
tblRMAData.RMANumber,
tblRMAData.JobName,
tblRMAData.Date,
tblFailureReasons.LintItemID,
tblLineItems.Qty,
tblLineItems.Model,
tblLineItems.ReportDate,
tblFailureReasons.FailureReason,
tblTestComponentFailures.ComponentID,
tblTestComponentFailures.FailureCause
FROM
tblRMAData INNER JOIN ((tblLineItems INNER JOIN tblTestComponentFailures ON tblLineItems.ID = tblTestComponentFailures.LineItemID) INNER JOIN tblFailureReasons ON tblLineItems.ID = tblFailureReasons.LintItemID) ON tblRMAData.RMANumber = tblLineItems.RMANumber
WHERE
(((tblFailureReasons.FailureReason) <> N'NONE'))
ORDER BY
tblFailureReasons.FailureReason
Alter Procedure usp_rptQualityReport2 As
exec usp_rtpQualityReport
SELECT
usp_rptQualityReport.RMANumber,
usp_rptQualityReport.JobName,
usp_rptQualityReport.Date,
usp_rptQualityReport.LintItemID,
usp_rptQualityReport.Qty,
usp_rptQualityReport.Model,
usp_rptQualityReport.ReportDate,
usp_rptQualityReport.FailureReason,
usp_rptQualityReport.ComponentID,
usp_rptQualityReport.FailureCause,
(SELECT COUNT(FailureReason) FROM usp_rptQualityReport a WHERE a.FailureReason=usp_rtpQualityReport.FailureReason ) AS groupingLevel
FROM usp_rptQualityReport;
View 3 Replies
View Related
Jun 9, 2007
is there a way to get a count of records for each table in a database by table in one query? I can query each table using a count, but this is pretty tedious when you have 50+ tables. Anybody have any ideas?
View 4 Replies
View Related
Oct 21, 2015
I am trying to write a query that will retrieve all students of a particular class and also any rows in HomeworkLogLine if they exist (but return null if there is no row). I thought this should be a relatively simple LEFT join but I've tried every possible combination of joins but it's not working.
SELECT
Student.StudentSurname + ', ' + Student.StudentForename AS Fullname,
HomeworkLogLine.HomeworkLogLineTimestamp,
HomeworkLog.HomeworkLogDescription,
ROW_NUMBER() OVER (PARTITION BY HomeworkLogLine.HomeworkLogLineStudentID ORDER BY
[Code] ...
It's only returning two rows (the students where they have a row in the HomeworkLogLine table).
View 3 Replies
View Related
Oct 26, 2006
Before I start driving myself nuts, I'd like to make sure my approach is correct.I want to create a simple job posting board.I have a text boxes for company name, email, job title, and job description, and a submit button.I created a table in my database called "JobPostings", with columns called "CoName", "CoEmail", "JobTitle", and "JobDesc"When someone fills out the fields and clicks submit, it will insert the new records.So,1. Is this the correct approach so far?2. What is the best way to display the job listings? A grid view?3. At submit time, how can I include that day's date?4. How can I get the records in the database to delete after 90 days?Thanks.
View 1 Replies
View Related
Jul 9, 2007
HI all I have this table below and i need to get records from col1 >1 and col2 >1
col1 col2 language
1 a s
1 b p
3 c e
3 c f
SELECT col1, col2, language COUNT(*) AS Expr1
FROM table1
GROUP BY col1, col2
HAVING (COUNT(*) > 1)
with the above query it gives me this
col1 col2 expr1
3 c 2
the language column is not displayed what i want is not the exact count but all the records which is duplicate and must resemble the result below
col1 col2 expr1 language
3 c 2 e
3 c 2 f
I need the result above inorder to normalize the table and bring out the language part into another table
thanks
View 2 Replies
View Related
Aug 9, 2007
Hi,
I would like to know how would i display following data.
exa:- (Note:- I wrote '---' so to display properly in to the forum
Table1
ComputerName--IpAddress
ABC1-----------10.1.2.3
ABC1-----------10.1.2.4
ABC1-----------10.1.2.5
ABC2-----------10.1.2.6
ABC2-----------10.1.2.7
ABC3-----------10.1.2.8
ABC3-----------10.1.2.9
ABC4-----------10.1.2.10
Table2
ComputerName--NetCard
ABC1-----------<card1>
ABC1-----------<card2>
ABC2-----------<card3>
ABC2-----------<card4>
ABC3-----------<card5>
ABC3-----------<card6>
Table3
ComputerName--Model
ABC1-----------DEll
ABC2-----------DEll
ABC3-----------DEll
Now i want output should be displayed like this
ComputerName--IpAddress--NetCard Model
ABC1-----------10.1.2.3---<card1>---DEll
----------------10.1.2.4---<card2>
----------------10.1.2.5
ABC2-----------10.1.2.6---<card3>---DEll
----------------10.1.2.7---<card4>
ABC3-----------10.1.2.8---<card5>---DEll
----------------10.1.2.9---<card6>
ABC4-----------10.1.2.10
Black filled should not displayed anything
Is it possible
--------------------
Thanks and regards
Abhishek Joshi
India- Bangalore
View 4 Replies
View Related
Sep 2, 2013
I'm having difficulty writing a sql query where i need to get the parent records for a record all in the same table
Example:
ID PARTOF TITLE DESCRIPTION
1 0 HOME HOME PAGE
2 1 SERVICES SERVICE PAGE
3 1 ABOUT US ABOUT US
4 3 ABOUT US:CONTACT CONTACT US
5 4 ABOUT US:CONTACT:MAP MAP
SO FOR EXAMPLE FOR RECORD ID 5 THE QUERY WILL GET ME ALL THE PARENT PAGES RELATED TO IT. IT WILL GET ID 4 AND 3.
View 1 Replies
View Related
Oct 12, 2013
I am importing data from a paradox table and trying to clean it up. I have this query that finds all the child records that are not in the parent table.
Select MemberID
FROM memtype AS a
WHERE NOT EXISTS
(SELECT *
FROM members AS b
WHERE a.MemberID IN (b.MemberID));
Now I'm trying to delete all those child records instead of just selecting them so I tried...
Delete MemberID
FROM memtype AS a
WHERE NOT EXISTS
(SELECT *
FROM members AS b
WHERE a.MemberID IN (b.MemberID));
Sql clearly doesn't like this
View 1 Replies
View Related
Dec 11, 2013
I have a parts table which has partid (GUID) column and parentpartId (GUID) column. Need to copy the records to the same table with new GUIDs for partids. How to do that? cursor or temp tables?
View 5 Replies
View Related
Feb 24, 2014
I have an application that has an existing query which returns org units (parent and child) from organization table with orderby on createddate + orgid combination.
Also I added another log table Organization_log with exact columns as Organization table and additional 'IS_DELETED' bool column.
WITH Org_TREE AS (
SELECT *, null as 'IS_DELETED', convert (varchar(4000), convert(varchar(30),CREATED_DT,126) + Org_Id) theorderby
FROM Organization WHERE PARENT_Org_ID IS NULL and case_ID='43333'
[code]...
I need to modify the query:
1. To display the records both from the Organization table and Organization_Log table.
2. The orderby should be sorted on 'Organization Name' asc and it should follow the child order in alpha sort as well.
E.g.:
aaa
==>fff
==>ggg
bbb
==> aaa
==> hhh
eee
==> ccc
==> ddd
==> fff
View 5 Replies
View Related
Mar 13, 2008
Hi
i have a table named categorymaster
categoryid catname parentid
1 Boxing 0
2 Tennis 0
3 Basketball 0
4 MayWeather 1
5 Tyson 1
6 Clinton woods 1
7 RogerFederer 2
8 Micheal 3
9 Hingis 2
so if i give input say categoryid=1[This falls under main category-boxing]
i need to get result as
1 boxing [main category]
4 mayweather [sub category]
5 tyson [sub category]
6 clinton woods [sub category]
if i give categoryid=5[Note:Tyson]
result should be as
1 boxing [main category]
5 tyson [sub category]
hope u can get my question
Thanks in advance
View 2 Replies
View Related
Apr 28, 2006
Hello,
We have a complex functionality of migrating data from a single record into multiple parent child tables.
To give you an example, lets us assume that we have a single table src_orders in the source database. We have a parent Order table and a child OrderDetails table in the target database. We need to pick one row from src_orders and insert this row in the Order table, pick up its PK (which is an identity column) and then use this to insert rows (say 5) in the OrderDetails table.
Again, we go back to the source, take a row, insert it into Orders, pick up the Orders PK and insert n rows in OrderDetails.
As of now, we are using the following approach for achieving this functionality.
1. Get the identity generated from the target table and store both the source table id and the target table id in a recordset.
2. Use the recordset as the source to a foreachloop , using foreachADO enumerator
3. Use data flow tasks to get the fields from the parent table for the source id, that needs to be inserted into the target child table
In case I have not ended up confusing everyone, can anyone validate this or suggest a better approach? :)
Thanks,
Satya
View 3 Replies
View Related