Show Total Amount Of Rows In Specific Table

May 20, 2012

I need to show the total amount of rows in a specific table?

The query is as follows:

As part of the planning process to expand the database that supports Northwind operations, the IT manager would like to know how many rows are currently in specific tables so that he can conduct capacity planning.

The results needed include two columns, TableName( containing all the tables in the database and Rows, which contain the total amount of all the rows per table).

View 4 Replies


ADVERTISEMENT

Calculating The Total Amount Of Drugs Prescribed, Total Amount

Aug 10, 2006

Hi all,



I have a table named Prescription that consists of attributes like PatientId, MedicineCode, MedicineName, Prices of different drugs, quantity of different drugs(e.g 1,2,3,10), date .

I would like to get a summary of the total number and amount of different drugs in a specific period, the total amount of each type of drug.



I kindly request for help.

Thanx in advance.

Ronnie

View 4 Replies View Related

Total Amount Of Triggers Per Table

Aug 1, 2006

Hi

Just a quick question taht I hope someone can answer, What it the total amount of trigger per table?

I understand that it is better to have as least as possible for performance

Thanks Rich

View 5 Replies View Related

Updating A Specific Amount Of Records In A Table

Jun 20, 2007

I have a table with 35,000 records in it. I want to update a value in column A for only the first 5000 records, leaving the value in Column A for the remaining 30,000 records as it is now. What would be the command I would use to update Column A for the first 5000 records.

Thanks,

View 4 Replies View Related

Locking A SQL Table For Specific Amount Of Time.

Feb 28, 2008

I am wondering if it€™s possible to lock a SQL table for specific amount of time, say 5 min.



There is a particular 'Phone' table on the database that should never get locked. Yet, during the development stages we have noticed that the table gets locked at time. The issue since has been resolved to the best of our ability, but, there is still a vague chance that the table can get locked due to the multiple jobs that query the table, when we go live.



If such a situation occurs, we just want to be able flip the switch that will send the server to the mirror mode and the previously mirrored database to become the principal.



So, I just want to recreate a situation by voluntarily locking the table.

Any help in this regard will be appreciated.

View 6 Replies View Related

Total Page Writes/total Amount Of Data Change In A Set Period Of Time

Jun 9, 2004

Does anyone know how I can determine the number of page writes that have been performed during a set period of time? I need to figure out the data churn in that time period.

TIA

View 5 Replies View Related

Transact SQL :: Show (0) Amount For A Month If No Data Exists In The Table For That Month?

Nov 9, 2015

I have two tables Costtable (Id,ResourceId, Amount,Date) and ResourceTable (ResourceId,Name) which shows output as below.

I want to show 0 amount for rest of the name in case of September. For e.g. if rest of the Resources does not appear in cost table they should appear 0 in amount

My Desired output

My current query

SELECT
RG.Id AS Id,
RG.Name AS Name,
ISNULL(SUM(AC.Amount), 0) AS Amount,
RIGHT(CONVERT(varchar(10), AC.[Date], 105), 7) AS [YearMonth]

[Code] ....

View 6 Replies View Related

Get Total Amount By Date

Apr 10, 2008

this is my sample records in my table


Sdate Amount
January 2007 250000.00
March 2007 300000.00
January 2008 350000.00



how can i get the amount from February to
May 2007 and sum it up like this


Details
February 2007 250000.00
March 2007 300000.00
April 2007 300000.00
May 2007 300000.00



this is all that i want to get
Total Amount: 1,150,000.00

i also posted this one in new to sql server section, but im hoping to get others opinion here. thnx

View 4 Replies View Related

How To Know When Total Salary Is At Certain Amount

Jul 24, 2013

I would like to know how to use a criterion on this example. I want to know only when the total salary is at a certain amount

SELECT SUM (salary) as Total Salary
FROM employees
WHERE Total Salary > 25000; ---this is where i am having issue

View 4 Replies View Related

SQL Call To Count The Total Rows In Table B For Each User In Table A

Jan 17, 2006

I have 2 tables:
 
TableA:
Name
UserA
UserB
UserC
 
Table B:
Name               Data
UserA              xxx
UserB              asdasd
UserB              ewrsad
UserC              dsafasc
UserA              sdf
UserB              dfvr4
 
I want to count the total entries in Table B for every user in Table A.  The output would be:
 
Name               Count
UserA              2
UserB              3
UserC              1
 
I can use a Select Count statement, but I will have to make a SQL call for every user in Table A.  Also, Table A is dynamic, so the users are always changing.  Can this be incorporated into one SQL call to count the total rows in Table B for each user in Table A?

View 5 Replies View Related

How To Get Specific Rows From Table

Aug 24, 2006

hi

i m using row count in order to get first 16 rows from a specific table...now i want to get rows from row no. 16 to 32 (or any no which i want)...can any one tell meee how can i query it using sql server 200





View 3 Replies View Related

Running Total: Summing On Distinct Amount

May 9, 2007

I am trying to do a sum on a goal amount that is repeated for each record. But what is the the forumla to only sum on the distinct goal amount.



Example:

Month Year Goal Other Value

March 2007 500 5568

March 2007 500 5568

March 2007 500 5569

April 2007 600 5568

April 2007 700 5569



Total (I am receive)

March-April 2007 1600 5568

March-April 2007 1200 5569



Total (I excpect)

March-April 2007 1100 5568

March-April 2007 1200 5569



I haven't found anything online to help.



Thanks,

Sam

View 5 Replies View Related

Transact SQL :: Total Count Of Table Rows

Sep 15, 2015

I have a question regarding the total count of the table rows

Select count (name) from test. Lets say I have got 200 count. And Select count (lastname) from test1.I have got 200.And this counts I should store it in "There are nn name items awaiting your attention and nn pending lastname's awaiting your approval".

So now I have to store the count in 'nn'.

View 5 Replies View Related

Get Specific Rows From A Table (using Max And Group By)

Apr 8, 2008

Hi

I'm no expert at SQL, I can't figure out how to solve that problem.

The following statement:

SELECT TeamMemberId, max(MonthId) as MonthId
FROM Position
INNER JOIN [Month] m
INNER JOIN [Year] y ON m.YearId = y.Id AND (y.Id = @yearId OR y.Id < @yearId)
ON m.Id = Position.MonthId
GROUP BY TeamMemberId


Returns
TeamMemberId MonthId
1 14
6 20

which is exactly what I want: The greates MonthId for each TeamMember in a specific year.

But I need more data for this postition, like the PositionName.

But the statement:

SELECT TeamMemberId, max(MonthId) as MonthId, PositionName
FROM Position
INNER JOIN [Month] m
INNER JOIN [Year] y ON m.YearId = y.Id AND (y.Id = 2 OR y.Id < 2)
ON m.Id = Position.MonthId
GROUP BY TeamMemberId, PositionName

Returns
TeamMemberId MonthId PositionName
1 2 ***. d. Geschäftsführung
6 20 ***. d. Geschäftsführung
1 14 CEO
6 16 CEO

The red rows are the ones I wanna get.
How can I achieve that only the max. MonthId rows are returned, even when I need to select more columns?
Any help is much appreciated.


Regards,

Stefan

View 6 Replies View Related

SQL Server 2012 :: Reset Running Total To X Amount

Dec 22, 2014

I need to calculate running total which resets when reached to 16, and also needs to calculate remaining amount as paid.

I have attached sample data, so we have two columns Earned, and Used. And we need to calculate Balance, and Paid columns.

View 4 Replies View Related

How To Select Specific 2 Rows Out Of A Huge Table

Jul 24, 2013

I have a very large table , and from that table I need just 2 records with column1 = 'A' and column1 = 'B' .

Here I don't think if I can not use OR or IN or Case operators because I need exactly 2 records not more.

View 6 Replies View Related

Which Is The Most Efficient Query To Find Out The Total Number Of Rows In A Table?

Mar 30, 2006

which is the most efficient query to find out the total number of rows in a table other than using - SELECT COUNT(*) ... in query

View 10 Replies View Related

Power Pivot :: Update Specific Rows In Table

May 21, 2015

I have a text file which I used as an initial load of data, containing a little over a million rows. I would like to produce a daily "update" file, which contains changes to existing rows, and creation of new rows. What is the best method of updating the existing table in power pivot?

View 2 Replies View Related

SQL Server 2012 :: Split Total Days And Amount Between Start And End Month

Mar 21, 2015

I have the table below and like to create a view to show the no of days the property was vacant or void and rent loss per month. The below explanation will describe output required

For example we have a property (house/unit/apartment) and the tenant vacates on 06/09/2014. Lets say we fill the property back on 15/10/2014. From this we know the property was empty or void for 39 days. Now we need to calculate the rent loss. Based on the Market Rent of the property we can get this. Lets say the market rent for our property is $349/pw. So the rent loss for 39 days is 349/7*39 = $1944.43/-.

Now the tricky part and what im trying to achieve. Since the property was void or empty between 2 months, I want to know how many days the property was empty in the first month and the rent loss in that month and how many days the property was empty in the second month and the rent loss incurred in that month. Most of the properties are filled in the same month and only in few cases the property is empty between two months.

As shown below we are splitting the period 06/09/2014 - 15/10/2014 and then calculating the void days and rent loss per month

Period No of Void Days Rent Loss
06/09/2014 - 30/09/2014 24 349/7*24 = 1196.57
01/10/2014 - 15/10/2014 15 349/7*15 = 747.85

I have uploaded a screenshot of how the result on this link: [URL] ....

Declare @void Table
(
PropCode VARCHAR(10)
,VoidStartDate date
,LetDate date
,Market_Rent Money

[Code].....

View 4 Replies View Related

Total Record Count - Pagination With Total Rows

Jul 26, 2013

My table contains 1000 records,

I need to know the total record count with the below paging query

SELECT EmpName,Place
FROM EmplyeeDetails ORDER BY Place
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

How to get?

View 2 Replies View Related

How Rows Get Deleted Auomatically In Sql Table After Specific Interval Of Time

May 17, 2008

hi there
i am using .net framework 1.1 with SQL 2000 .
i want the data in table to get deleted automatically after 30 days of inserting data.
so how do i achieve this?

View 4 Replies View Related

Retrieving Specific Page(number Of Rows) Form Table

Sep 27, 2005

hi,

i need SP that receive 2 integers ,@NUM_ROWS and @PAGE_NUMBER,
and return the rows in that page.
for example:

SP(4,2) will return 4 rows in page number 2 .

So if i have table with 9 rows i will get rows 5-8,
the first page is rows 1-4 the second page is 5-8 and the 3 page is row 9.

i have to assume that rows can be deleted form that table.
thanks

View 3 Replies View Related

Transact SQL :: Select Specific Values From All Rows Where Value Of A Specific Column Is (Active)

May 23, 2015

I need to select specific values from all rows where the value of a specific column is "Active"

This part works: SELECT LastName, FirstName, MiddleInit, ClientId FROM dbo.Client

But I want to add: WHERE StatusType = (Active) and how to do this.

View 4 Replies View Related

Show The Rows Containing Same Values Rate As Column In Html Table

Mar 27, 2008

Hi All,

I have following problem
please help me to resolve it.

There is table for shipping
as follows:




City


Kg


Rate(rs)




Rajkot


1


25.00




Rajkot


2


30.00




Rajkot


3


42.50




Ahemedabad


1


42.50




Ahemedabad


2


55.00




Ahemedabad


3


67.50




Ahemedabad


4


80.00




Goa


1


90.00




Goa


2


105.00




Goa


3


120.00




Gondal


1


25.00




Gondal


2


30.00




Gondal


3


42.50




Morbi


1


25.00




Morbi


2


30.00




Morbi


3


42.50




Gandhinagar


1


42.50




Gandhinagar


2


55.00




Gandhinagar


3


67.50




Gandhinagar


4


80.00




 We need to display
the table in html page using any Asp.Net language.

The result table should be:




City(s)


1


2


3


4




Rajkot,Gondal,Morbi


25.00


30.00


42.50


-




Ahemedabad,Gandhinagar


42.50


55.00


67.50


80.00




Goa


90.00


105.00


120.00


-




Baruch


70.25


80.00


92.00


120.50




 Description:

- Display all the cities
together for which all the shipping charges for (1-4) kgs are same. The range
of kgs is not fixed. 

Reply as soon as possible.

Thanks

View 3 Replies View Related

Can Profiler Show Table Rows Affected By I/U/D Action From Within Stored Procs?

Jun 13, 2007

Hello. I was using the new sys.dm_db_index_operational_stats function which is nice for seeing counts of insert/update/delete actions per table index, bla bla bla... Anyways, question, can I do the same thing with Profiler? meaning, can I trace stored procs and sopmehow see the proc exec WITH each table it does actions against? Not talking about filtering on table names in the text, talking I just want to run an application, which uses all stored procs, and see every table used by that execution of the proc, and also the number of rows inserted,updated,deleted.... If so, which Profiler events/columns must I flick on to gather that? Thanks, Bruce



View 4 Replies View Related

Specific SQL For Total Billing

Apr 10, 2008

My DB has the following description

Table & Columns: ID field is identity. Currency is either CDN or USD.
Order: ID, DateOrder, Currency, AmountOrder
Invoice: ID, NumOrder, DateInvoice, AmountInvoice
Credit: ID, NumOrder, NumInvoice, DateCredit, AmountCredit
Rate: YYYY, MM, US


Relationship among tables:
Order & Invoice : on Order.ID = Invoice.NumOrder, one to many
Order & Credit: on Order.ID = Credit.NumOrder, one to many
Invoice & Credit: on Invoice.ID = Credit.NumInvoice, one to many

Other conditions:
For orders of currency USD, the rate of the DateOrder is used to convert the Order.AmountOrder to CDN.
The rate of DateInvoice is used to convert the Invoice.AmountInvoice to CDN.
A credit always uses the rate of DateInvoice of its associated invoice.

Report: Total billings of USD sales in a period of time (startDate, endDate), converted to CDN

Total billing of USD sales converted to CDN = sum [AmountInvoice * rate of DateInvoice – sum(AmountCredit * rate of DateInvoice)]
WHERE the DateInvoice and DateCredit must be in the specified period.
AND associated Order.Devise = USD


SQL:
1 SELECT SUM (AmountInvoice*dbo.GetRate(DateInvoice) – SUM (AmountCredit*dbo.GetRate(DateInvoice)) as TotalBilling
2 FROM Order IINNER JOIN Invoice ON Order.ID = Invoice.NumOrder
3 LEFT JOIN Credit ON Invoice.ID = Credit.NumInvoice
4 WHERE Order.Devise = ‘USD’
5 AND DateInvoice >= startDate AND DateInvoice <= endDate
6 AND DateCredit >= startDate AND DateCredit <= endDate

Actual Results: Only orders which have both invoices & credits. The orders which invoices but no credit are not included in the results.


How can I correct this, please?

View 5 Replies View Related

Have Total Show Under User

Sep 23, 2015

(SQL Server 2008) I want to display a total for each user that is returned from my query result. I was trying the union all route as that is what I am most comfortable with, however, the result set returns the total row at the bottom instead of under each user. This is the desired result set...Here is my query, what do I need to alter to have the output display like so?

Code:
Create Table #Test
(
id varchar(50),
name varchar(500),
out1 int,
out2 int,
total4day int,

[code]....

View 5 Replies View Related

Show Total And Details

Apr 8, 2008

Hi

I have a categories table and a products table, the products are associated with a category, and my current select statement looks like this..

SELECT COUNT(dbo.tbl_Categories.CatName) AS TotQty, dbo.tbl_Categories.CatName
FROM dbo.tbl_Products INNER JOIN
dbo.tbl_Categories ON dbo.tbl_Products.CatID = dbo.tbl_Categories.ID
GROUP BY dbo.tbl_Categories.CatName

this give me ..

TotQty CatName
1 Books
2 DVD

I wonder how I can change the select statement so I retrieve a result like this..

TotQty CatName Description
1 Books Oliver Twist
2 DVD Dire Straits
2 DVD Elvis

View 20 Replies View Related

Transact SQL :: Show Total For Each Name

Sep 23, 2015

I tried my syntax below, but it said that the field were not in the group by.  This is syntax and for each of the 2 names (only small subset of real data) I need a Total column to display between each different person like so:

Total, 29, 150, 2400/60
Total, 25, 143, 2400/60

Here is syntax:

Create Table #Farquard
(
empID varchar(50),
fullname varchar(500),
break1 int,
break2 int,
dailytotal int,
workday date
)

[Code] ......

View 8 Replies View Related

Show All Except Specific

May 26, 2013

I want to select all those record except those record who have birthdate less then 4/13/1992 and relation= son

I tried this

SELECT * FROM Dependents WHERE code = 2
AND NOT CONTAINS
(SELECT *
FROM Dependents
WHERE BirthDate < '4/13/1992' AND relation= 'son')

But not working....

View 4 Replies View Related

T-SQL (SS2K8) :: How To Show Total Sales

Mar 10, 2014

My Table struct

create table cust
(
cust_id int ,
city varchar(20),
pincode int,
sales int,
latitude float,
longitude float

[code]....

like i wanna display each pincodes how to make a code?

View 6 Replies View Related

Matrix - Hiding A Specific Row In Column Total

Feb 1, 2007

Hi,

I have created a matrix with multiple rows in the main data cell and a subtotal at the end of the row. The first row in my matrix main cell is just a count of records, whereas the 2nd row is a % of the value in the 1st row compared to the total of that row. I have 5 columns in the matrix as below (only first row is shown plus the total row. Ignore any rounding issues):










Status
A
B
C
D
E
Total

02/01/2007
No.
9
32
3
13
0
57

% Total
15.00%
56.00%
5.00%
24.00%
0.00%
100.00%

Total
No.
77
143
25
72
2
319

% Total
135.00%
350.00%
48.00%
250.00%
2.00%
556.00%

I have a total column at the end. I want to hide the %Total row within the total row as it does not make sense in this context. Anyone know how to achieve this?

Thanks

View 4 Replies View Related

Sum Function - Show Total Quantity For A Product

Sep 5, 2015

I am trying to create a manifest for one of our clients and want to show the total qty for a product.

Below is my query, the quantity is listed below as TL.Quantity but I think I need to use a SUM function to show the total by product?

Select
TH.TransactionDate,
TH.Reference,
TransactionAccountNumber.AccountNumber as [Client Ref],
Product.Code,
Product.Description,

[Code] ....

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved