I have a table called Employees which has lots of columns but I only want to count some specific columns of this table.
i.e. EmployeeID: 001
week1: 40
week2: 24
week3: 24
week4: 39
This employee (001) has two weeks below 32. How do I use the COUNT statement to calculate that within these four weeks(columns), how many weeks(columns) have the value below 32?
If I just use a simple select statement, I find that I have 8286 records within a specified date range.
If I use the select statement to pull records that were created from 5pm and later and then add it to another select statement with records created before 5pm, I get a different count: 7521 + 756 = 8277
Is there something I am doing incorrectly in the following sql?
DECLARE @startdate date = '03-06-2015'
DECLARE @enddate date = '10-31-2015'
DECLARE @afterTime time = '17:00'
SELECT
General_Count = (SELECT COUNT(*) as General FROM Unidata.CrumsTicket ct
[Code] ....
I have a table called Employee which have 6 columns. This table are having 1000 records. Now I want to know the distinct value count of all these 6 columns as well as normal count. like this:
ColumnName DistinctCount NormalCount
Id 1000 1000
Name 1000 1000
Phone 600 600
PINCode 200 1000
City 400 1000
Gender 2 1000
I am using stored procedure to load gridview,i want to show row specific values in coloumns , as i an working on daily timetable of college and There are three tables Week_Day,Daily_Timetable & Subject.Daily_Timetable has data which has week_day,class_id,Subject_id,Period_No.
Each day has 6 periods and each period is mapped with subject in daily timetable.From below sql i am getting 6 rows of monday.
But i want to show in a row weekname,period1_subject_id(Period_No=1),period2_subject_id(Period_No=2),period3_subject_id.......upto
period6_subject_id.
Please see my query below:-
SELECT Week_Day.Week_Day_name, Subject.Subject_Code, Daily_Timetable.Period_No
FROM Week_Day LEFT JOIN
Daily_Timetable ON Week_Day.Week_Day_Id = Daily_Timetable.Week_Day_Id and Daily_Timetable.Class_Id=6 LEFT JOIN
Subject ON Daily_Timetable.Subject_Id = Subject.Subject_Id order by Week_Day.Week_Day_Id ,Daily_Timetable.Period_No
who has workflows created and ordered by CreateTimestamp . i need to count the number of workflows from top to point where there is either a success or failure workflow that occurs at the latest .
1.ban 137108351 has success workflow and prior to that workflow it has 2 workflows (exclude success and failure)
2.ban 104917284 has success workflow as latest (it still has failure but not considered because it is occurred earlier to success ) and prior to that workflow it has 2 workflows (exclude success and failure)
3.ban 107500674 has failure workflow and prior to that workflow it has 0 workflows (exclude success and failure)
below provided code for sample data as well
GO
/****** Object: Table [Temp].[deleteit] Script Date: 7/31/2015 3:04:55 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [Temp].[deleteit](
[ban] [nvarchar](256) NULL,
[code]....
I am trying to count the number of value "99" for each column in the whole table
to see how many "99" are there per column for the whole table...
and get the percentage per each column.. How can I do this?
output should look like
Column A 117 10%
Column B 120 14%
-- etc..
I'm working on a data analysis involving a table with a large number of records (close to 2 million). I'm using only three of the columns in the table and basically am grouping results based on different criteria. The three columns are PWSID, Installation and AccountType. I have to Provide the PWSID column with a count of the total number of installations per PWSID, also a count of AccountTypes per PWSID. I have the following query, but the numbers aren't adding up and I'm not sure why. I'm falling short in the total count by around 60k records.
CREATE TABLE [dbo].[CATASTRO_PSWID_SHPMUNINEW](
[Installation] [numeric](38, 8) NULL,
[AccountType] [nvarchar](50) NULL,
[PWSID] [smallint] NULL,
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
[code]....
I am using SQL 2012. I have a GROUP BY and I want to select two other fields from my table at the same time: One column that is a string (account_code) and one that I need to perform a count on (customer_number). I know the code COUNT(DISTINCT customer_number) works for getting that. I need to select both of those fields on top of what I have. I have the following:
DECLARE @Providers TABLE (ID INT IDENTITY(1,1),
Provider_Name VARCHAR(20),
Uniq_Id VARCHAR(10),
Total_Spent MONEY,
Total_Earned MONEY)
INSERT INTO @Providers (Provider_Name, Uniq_Id,Total_Spent,
Total_Earned)
[Code] .....
is it possible to use the window functions to count the number of rows in the previous 24hours from the current row.
I have a table of events like:
User, TimeStamp, etc...
I want to identify the row which is the event number 50 in the past 24 hours.
does the window functions can do this? and how?
the ROW PRECEDING etc... appear to not have enough feature to support time related function.
Stream-insight is able to create this type of query, but I have to do it directly in SQL.
There are two tables
TABLE 1 (NAME - Groupseats)
id session course groupcode sub1 sub2 sub3
1 2015 ba1 137 HL EL Eco
2 2015 ba1 138 EL SL HS
3 2015 ba1 139 SL EL His
From this table i use to admit a student and select their choice of group simultaneously all the subjects associated with GROUP is save on another table.
Here is the TABLE 2 Structure and sample data:
table 2 (NAME - tblstudetail)
id studentID session course sub1 sub2 sub3
1 15120001 2015 ba1 EL SL HS
2 15120002 2015 ba1 HL EL Eco
3 15120003 2015 ba1 SL EL His
4 15120004 2015 ba1 HL EL Eco
AND so no..........................
Now i just want to COUNT the Number of Groups Filled in tblStudateil.
I am getting an exception. And i don't know whether this exception is from the C# coding or SQL SERVER."Cannot roll back InsertRxs. No transaction or savepoint of that name was found.Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.A transaction that was started in a MARS batch is still active at the end of the batch. The transaction is rolled back."
View 2 Replies View RelatedI have the following query
WITH summary AS
(SELECT tu.SequenceNumber,
tu.trialid,
tu.SBOINumber,
tu.DisplayFlag,
[Code] ....
I am having trouble with the RowNumber Over Partition By portion of the query. I would like the query to return only the first occurrence of each sboinumber in the table for each trial id. It is only giving me the first occurrence of each sboinumber. I tried including the trialid in the partition by clause, but that is not working.
Sample Data
SequenceNumber TrialID SBOINumber
1 1 5000
2 1 5000
3 2 5000
4 2 5000
5 1 5001
6 3 5001
7 3 5001
Should return SequenceNumber 1 and 3, 5, 6
Hi
I want to enter rows into a table having more number of columns
For example : I have one employee table having columns (name ,address,salary etc )
then, how can i enter 100 employees data at a time ?
Suppose i am having my data in .txt file (or ) in .xls
( SQL Server 2005)
Using the following tables and data---
CREATE TABLE tblRiskReviewHistory(RiskReviewID int, RiskReviewHistoryID int, Name nvarchar(20), Description nvarchar(50), Date date)
INSERT tblRiskReviewHistory(RiskReviewID, RiskReviewHistoryID, Name, Description, Date)
VALUES(1,1,'Customer A','Profile Assessment','01/01/2015'),
(1,2,'Customer B','Profile Assessment','02/20/2015')
[Code] ...
And currently outputs;
Name Description Date Question Answer
Customer A Profile Assessment 01/01/2015
How complex is the structure?
Customer A
Profile Assessment
01/01/2015
The total value of assets?
Less than GBP 1 million
Customer A
Profile Assessment
01/01/2015
The volume of transactions undertaken?
Low (-1 pmth)
[Code] ....
However, I would like it to output;
Name
Description
Date
How complex is the structure?
The total value of assets?
The volume of transactions undertaken?
How was the client introduced?
Where does the Customer reside?
[Code] ....
The number of questions are unknown for each RiskReviewID and they can be added to in the future.
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
I am still learning T-SQL .Lets consider the table below, ID 1-3 shows our purchase transactions from various Vendors and ID 4-6 shows our payments to them
Table 1 - VendorTransactions
ID PARTY AMOUNT VOUCHER
---------------------------------------
1 A 5000 Purchase
2 B 3000 Purchase
3 C 2000 Purchase
4 A 3000 Payment
5 B 1000 Payment
6 C 2000 Payment
7 A 1000 Payment
Now we have a blank table Table 2 - Liabilities
ID PARTY AMOUNT
I want that SQL should look for each individual party from Table 1 and Calculate TOTAL PURCHASE and TOTAL PAYMENTS and then deduct TOTAL PAYMENTS from TOTAL PURCHASE so we get the remaining balance due for each party and then add the DIFFERENCE AMOUNT alongwith PARTY to the TABLE 2 so I can get the desired result like below
ID PARTY AMOUNT
-------------------------
1 A 1000
2 B 2000
3 C 0
I have an table with duplicate data. I need to delete the duplicate records based on the RequestId.
I want to delete the records based on the RequestID 1001, Delete the duplicates in 1002, 1003 .
DECLARE @table TABLE ([Employee_SID] [int] NOT NULL,
[CalculatedTotalSalesYear] [numeric](19, 2) NULL,
[CalculatedTotalSalesYearAnnualized] [numeric](19, 2) NULL,
[RequestID] [int] NOT NULL)
[Code] ....
Expected Result
select * from @table
I have a business need to create a report by query data from a MS SQL 2008 database and display the result to the users on a web page. The report initially has 6 columns of data and 2 out of 6 have JSON data so the users request to have those 2 JSON columns parse into 15 additional columns (first JSON column has 8 key/value pairs and the second JSON column has 7 key/value pairs). Here what I have done so far:
I found a table value function (fnSplitJson2) from this link [URL]. Using this function I can parse a column of JSON data into a table. So when I use the function above against the first column (with JSON data) in my query (with CROSS APPLY) I got the right data back the but I got 8 additional rows of each of the row in my table. The reason for this side effect is because the function returned a table of 8 row (8 key/value pairs) for each json string data that it parsed.
1. First question: How do I modify my current query (see below) so that for each row in my table i got back one row with 19 columns.
SELECT A.ITEM1,A.ITEM2,A.ITEM3,A.ITEM4, B.*
FROM PRODUCT A
CROSS APPLY fnSplitJson2(A.ITEM5,NULL) B
If updated my query (see below) and call the function twice within the CROSS APPLY clause I got this error: "The multi-part identifier "A.ITEM6" could be be bound.
2. My second question: How to i get around this error?
SELECT A.ITEM1,A.ITEM2,A.ITEM3,A.ITEM4, B.*, C.*
FROM PRODUCT A
CROSS APPLY fnSplitJson2(A.ITEM5,NULL) B, fnSplitJson2(A.ITEM6,NULL) C
I am using Microsoft SQL Server 2008 R2 version. Windows 7 desktop.
I have a table that has for each shop a value that can change over time.For example
BK_POS 1 --> Segment A
BK_POS 1 --> Segment /
What I would like to achieve is to get all distinct Shops (BK_POS) from the table above, but if for that specific pos a row exists where the segment = "/" then I do not want to take this BK_POS in my select query.More concrete, the for example above I do not want to select BK_POS 1 because he has one row where the segment = "/".
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.
I have a problem where I have 2 compare 2 records from the same table. This part looks easy but the problem is for a User there can be multiple records and I have 2 compare each record with its previous instance based on the timestamp. Not only I have to compare I have to perform some analysis. Below is the Table script and sample output.
Givens: All SQL Server 2008 or 2012 tools at your disposal.
Production database contains the following tables (simplified for example: constraints ignored, etc.) associated with a racing video game’s server.
-- A player of our game
-- Table greater than 10 million rows
CREATE
TABLE [dbo].[User]
(
[UserId]
[bigint] NOT
NULL
,[country]
[int] NULL
-- User’s home country
,[name]
[nvarchar](15)
NULL -- User’s displayable name (‘John’, ‘Bill’)
,[subscriptionTier]
[int] NULL
)
-- 0 == free, 1 == paid, for instance
Assume that rows get written into the event tables at a rate of 1,000 a minute,are never updated once written and currently are only read on a replica/reporting server.
Question Background: Write up a single query that would return the following: List of users and whose “TotalMoneyEarned” value ever grew (between logon events) at a rate of more than 1,000 per minute (we’d consider these suspicious and flag them for later investigation).
For instance, if the sample data were:
-- example of [Events.UserLogon] data -- not the query output we want
EventId UserId TotalMoneyEarned LogonDate
----------- -------------------- ---------------- -----------------------
1 1 1000 2010-10-16 00:19:56.460
2 1 1500 2010-10-16 00:20:56.460
3 1 3000 2010-10-16 00:21:56.460
4 1 10000 2010-10-16 00:29:56.460
Event 1 is okay because there’s nothing to compare it against
Event 2 is okay because the TotalMoneyEarned only grew 500 in a minute
Event 3 should be flagged, as the value grew 1500 in a minute
Event 4 is okay, as it grew 7,000 in 8 minutes (< 1000 per minute)
Query Output (your query should return data in a format like this):
User Flagged Logon Time Rate Since Last Logon (money/minute)
John 2010-10-16 00:21:56 1500
Dave 2010-10-16 00:30:50 3200
Bill 2010-10-16 00:35:23 1000
It is likely that you will need to create sample data for both the User and [Events.Logon] tables. We are looking for a single query that returns data like what is represented in Query Output.
Hi guys,
Is there any function to get the total number of records in a specific table?
(SQL SERVER 2000).
Thanks in advance,
Aldo.
I have a table with a couple hundred billion records (sql server 2005). When I do a select count(*) from tblx -- it takes this side of forever. Is it possible to count partitions and then add them up to make it faster?
How I could improve the performance for count(*) of this huge table. Note: if the partition idea sounds viable -- what would that look like?
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'.
I have one table say A and in which 4 columns are there. Out of 4 , one columns stores the queries like
'select * from table xyz' etc(Only select queries). I am writing a procedure in which I have to fetch this column and execute the query and wants to check whether query i.e. "select * from table xyz" contains any record or not. If yes , I am updating the table B with value as Pass , else Fail.
I used execute @queryfromvariable but it does not gives me count..
I have a table with 4 column in below
Total amount = 1000
salemancode1 = space
salemancode2 = Staff-99
salemancode3 = space
salemancode4 = staff-88
How I can write a one query statement to do this, we expect to count how many salemancode is not space and count the number of salesman to over the total amount.
total amount / (no_of_saleman) as commission
the result is 1000/ 2 the commission is $500.
How can I determine the number of columns in a table?
View 3 Replies View RelatedI am currently working on an app and have an issue with a table in the database. The table has 10,000 records in it and a column is added that is to use a 4 digit in sequence. The datatype for the new column is varchar since no math will ever be done on the added column. If necessary, I can change the datatype but would prefer not to. The 4 digit would start with 0000 at ID 1 and go to 9999 at ID 10000. I'm thinking some type of update statement since it is updating each record but how would it be done sequentially?
View 9 Replies View RelatedHi,
I have a denormalized table (done so with reason) with around 40 columns. I would never have to retrieve data for all of those columns together.
I haven't done any performance measurements yet but just wondering if anyone has ready answer to this: Will there be a performance degradation if I retrieve data from a table with many columns, even if not all columns are referred in the query? (for making it simple, lets assume that all or varchar type of columns, I just want to find out if performance degrades if there are too many columns in table)
Thanks in advance,
Sandeep
We are looking at developing a new app with a SQL Server db. As such, we're all newbies.
What is the maximum number of columns I can put in a SQL Server table?
Thanks in advance.
Bob Anderson
I have a database hosted by GoDaddy. Recently they made some changes to the interface and upgraded to SQL Server 2008. One or the other has made it impossible to access my data in one table.
The table is quite large in terms of the numbers of elements. Each row describes a dog and all the elements are components of the description. There are (I would guess) more than 50 elements all together.
When I try to search the database, the query form goes beyond the top and bottom of the page. I can scroll the database but the search tool (which lies atop the data) does not scroll. The result is that I can't activate the search.
I've tried about 10 machines. All with IE6 display this fault. Machines with IE7 do not. I've tried various screen resolutions on the machines with IE6. That doesn't help.
I've checked other tables in the database. No problem.
In short, there's nothing I can do. I can't edit my data and GoDaddy says, "Tough."
Is there a limit on the number of columns (elements) in a table in SQL Server 2008?
Eric
I am creating a report that uses the Matrix control. I need to display a fixed number of columns (5). In my query, I am returning the top 5 rows of data. However, in some cases there are less than 5 rows of data returned from the dataset. Is there a way to force the number of columns displayed in the matrix control and to populate with some text (such as "n/a") if no data is available?
Thanks!
ads
How do I create a temporary table in a stored procedure with differeent number of columns?
That is: sometimes ten columns, sometimes 24 etc.