Problem Selecting Count, Comparing 2 Fields In Same Table
Jul 20, 2005
I have the following table structure.
group1 group2 group1_result group2_result
'One' 'Two' 3 2
'One' 'Two' 3 1
'One' 'Two' 2 5
'One' 'Two' 4 1
'One' 'Two' 0 5
I need to sum up the number of times 'One' is greater than 'Two', and
vice-versa. For example, the result I would like to achieve is as
follows.
group1 group2 group1_total group2_total
'One' 'Two' 3 2
I'm using the following SQL statement, but I get 5 rows returned,
giving me a '1' or '0' for each row.
select group1, group2
,sum(CASE WHEN group1_result > group2_result THEN 1 ELSE 0 END)
,sum(CASE WHEN group2_result > group12_result THEN 1 ELSE 0 END)
FROM table1
GROUP BY group1, group2
Any help would be greatly appreciated.
View 1 Replies
ADVERTISEMENT
Nov 29, 2014
I have two name columns in my table, NAME1 & NAME2 that I want to compare to see if they match. Only problem is that the order of the first, last, middle name can be either same or different between the two fields.
For example
NAME1 = JAY JOHN SMITH
NAME2 = JOHN SMITH JAY or SMITH JOHN JAY
Is there a way to somehow reorder these fields and then compare using SQL?
View 2 Replies
View Related
Jan 28, 2008
I have just started using SQL Server reporting services and am stuck with creating subreports.
I have a added a sub report to the main report. When I right click on the sub report, go to properties -> Parameters, and click on the dropdown for Parameter Value, I see all Sum and Count fields but not the data fields.
For example, In the dropdownlist for the Parameter value, I see Sum(Fields!TASK_ID.Value, "AppTest"), Count(Fields!TASK_NAME.Value, "CammpTest") but not Fields!TASK_NAME.Value, Fields!TASK_ID.Value which are the fields retrieved from the dataset assigned to the subreport.
When I manually change the parameter value to Fields!TASK_ID.Value, and try to preview the report, I get Error: Subreport could not be shown. I have no idea what the underlying issue is but am guessing that it's because the field - Fields!TASK_ID.Value is not in the dropdown but am trying to link the main report and sub report with this field.
Am I missing something here? Any help is appreciated.
Thanks,
Sirisha
View 3 Replies
View Related
Apr 6, 2008
Hi all
I have a table with one nText Field , some times i need to realize whether two records have the same value in that nText Field or not , so how can i compare
these two records based on their ntext Fields.? in the other words i'm looking for some thing like the follows:
declare @a nTextdeclare @b nText
select @a=MynTextField from MyTable where PK=18select @b=MynTextField from MyTable where PK=21
if @a=@b print 'Records Are the same'else print 'Record are not the same'
Thanks in advance.
Regards.
View 3 Replies
View Related
Sep 1, 2000
I need to build a cube for membership over time. Active membership is the gap from the record's CreationDate to SuspendedDate, as compared to a reference date (today's active members have CreationDates before today, and SuspendedDates > today, or NULL).
Any idea how to build a cube so I can count membership over a Time dimension? The only approach I've been able to come up with is to create a table with a record for each active member on each date (brute force).
AtDhVaAnNkCsE!
View 1 Replies
View Related
Aug 4, 2005
I am working with the article that MAK wrote on SecurityLogs http://www.databasejournal.com/features/mssql/article.php/3515886
I have completed this, but I have made some changes to the database (for normalization to 3NF purposes). I now have problems with a query.
I am trying to "Insert a new record in a table if it does not already exist in the table". To try to clarify I perform the following query:
INSERT INTO Tmp_Event
SELECT DISTINCT EventID, EventType, EventTypeName from Tmp
Which gives me the Tmp_Event table consisting of EventID's etc. (no duplicates). What I then want to do, is compare the 'Tmp_event' table and an already existing 'Event' table. These two tables are in fact identical. I would like to insert any records from 'Tmp_Event' into 'Event' if they do not already exist in 'Event'.
This query gives me all records that do not exist in 'Event'
SELECT EventID, EventType, EventTYpeName from Tmp_Event
WHERE EventID NOT IN (SELECT EventID from Event)
How can I change this query into performing an INSERT INTO Event as well?
Hope this makes sense :)
-Silia
View 2 Replies
View Related
Jan 17, 2002
I would like to compare 2 fields.. One of which is generated by the application upon insert. The other which is today's date.
In other words I want all records with today's date.
Problem is I get no results because I'm also apparently comparing the time.
Do I use a CONVERT function on my select & GETDATE outputs?
Thanks,
Kelly
View 2 Replies
View Related
May 22, 2002
I have a table with a DateTime field that has Dates in the format of mm/dd/yyyy I want to do a simple count on the table for the previous day but keep getting 0 for the results. Here is the script I've been trying.
SELECT COUNT(*)
FROM mytbl
WHERE Collected_Date = DateAdd (dd, -1, GetDate())
It appears to me that the time portion of the DateAdd function keeps the matches that I am looking for from happening. What am I missing or am I going about this all wrong?
Thanks for any help
View 1 Replies
View Related
Jan 28, 2008
I want to do something like this.
Code SnippetSELECT * FROM [scholarship] WHERE ([schlrPrefix] + ' ' + [schlrName] AS ScholarshipName) LIKE 'Ann Buttler'
How do I something like this where it combines two fields and then use that to compare to a parameter?
View 6 Replies
View Related
Apr 9, 2008
My situation is this: I have a requirement table,in the requirement table i have a field which is allow_multiple,if it allows multiple the value is 1 and 0 if it will not allow and also I have an insert storedprocedure for inserting licenses which will be added in the Staffl table.When i insert a new license for the staff,it should check in the requirement table if the new inserted lincense does have 0 or 1 value in the allow multiple field.If it has a value of 0 the new license should not be inserted.What would be the good way to compare the existing licenses of the staff and the newluy added?
Funnyfrog
View 1 Replies
View Related
Sep 20, 2005
Hi
I have a content management system that I am building. My CMS has a
pages table which contains 60 fields, some are varchars, some are ntext
some are dates etc. They have field names which match their purpose
like varchar1, ntext3 etc. These fields are used up depending on which
template is applied to the page.
The templatefields table holds holds the information about each field
in a template. It's control type (rich text control, date control etc)
and the field in the pages table that the data will be stored in
(varchar1,ntext3 etc)
What I want to be able to do is loop through the templatefeilds table
for a particular template and then using the field name for the pages
table, go to the pages table and get the value to populate the control.
So to clarify, I would like to produce a stored procedure which outputs
the page field name (varchar1) from the templatefields table and
varchar1's value in the pages table. Maybe in a temporary table.
Is this possible?
Any guidance would be appreciated..
Thanks in advance
View 1 Replies
View Related
Mar 16, 2006
Need syntax for selecting fields from 2 different datasets in one report.
Each dataset connects to a different server.
View 3 Replies
View Related
Jul 31, 2007
I have three user tables (identical structure) that holds customer information. Each has about 1200 records, give or take a dozen, 99% of which are identical. However, there are a few records in each table that differ. Im trying to select the differences in these tables using a query.Each customer has a custmer ID, but cust_id 1234 in table a may not be the same customer as cust_id 1234 in table b. Therefore, i am trying to compare using the cuLoginName field which is the username/email address text field. This is what differentiates the records.
I have tried using
select cust_id, cuLoginNamefrom tblCustomerAwhere NOT exists (select cuLoginName from tblCustomerB)
to get the different records between the two tables (i.e. those in A but not in B), but even though there are two users in A that do not appear in B, the results are coming back with nothing. I am guessing this is a problem comparing on text fields?
What is an effective way around this problem?
Thanks
View 3 Replies
View Related
Aug 18, 2004
We have three fields in a table: firstname, Surname & Organisation. Firstname & surname will always be filled but in most cases organisation is NULL.
Part of what these fields will be used for is a mailshot. If there is no organisation data then the mailshot will open with 'Dear Firstname Surname, ' but if the organisation is present they would like 'Dear Organisation'.
Is it possible to create a select state that checks the organisation field, and if a value is present return that value else return the firstname, surname combination? I have tried various things but I cannot get it to work.
I know the ideal situation would be to do this type of condition check at scripting level (PHP, ASP, Visual Basic) but my bosses would like to try it at SQL level.
View 1 Replies
View Related
Nov 30, 2003
I have an SQL statement that looks like the following:
SELECT [Docs-Entities].entityID, entityName, COUNT([Docs-DocsEntities].filename) AS numDocs
FROM [Docs-Entities] LEFT JOIN [Docs-DocsEntities]
ON [Docs-Entities].entityID = [Docs-DocsEntities].entityID
GROUP BY [Docs-Entities].entityID, entityName
but the problem is that numDocs (the COUNT) is not returning a distinct count. In the DocEntities table, a particular document can actually have multiple entries with the same entityID so that produces inflated numbers for numDocs. But when I do a SELECT DISTINCT on a particular entityID, the results are less and don't match the numDocs number because I only need to list the document one time. This is not a huge issue, but it looks bad on my site.
Is there a way that I can make COUNT count distinctly?
Thanks for the help and I hope I worded that cllearly...
View 2 Replies
View Related
Jan 9, 2008
Hi All,I'm having a problem writing an SQL statement that I can't quite wrapmy head around.First, the background:I have a journal subscription system including 3 tables,tblSubscription, tblTransaction and tblIssue, detailed below.tblSubscription:CREATE TABLE [dbo].[tblSubscription]([SubscriptionID] [int] NOT NULL,[SubscriberID] [int] NOT NULL,[Status] [int] NOT NULL,[JournalID] [int] NOT NULL,[Created] [datetime] NOT NULL,CONSTRAINT [PK_tblSubscription] PRIMARY KEY CLUSTERED([SubscriptionID] ASC)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY]tblTransaction:CREATE TABLE [dbo].[tblTransaction]([TransactionID] [bigint] NOT NULL,[SubscriptionID] [int] NOT NULL,[Copies] [int] NOT NULL,[IssueStart] [int] NOT NULL,[IssueEnd] [int] NOT NULL,[LastUpdated] [datetime] NOT NULL,CONSTRAINT [PK_tblTransaction] PRIMARY KEY CLUSTERED([TransactionID] ASC)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY]ALTER TABLE [dbo].[tblTransaction] WITH NOCHECK ADD CONSTRAINT[FK_tblTransaction_tblSubscription] FOREIGN KEY([SubscriptionID])REFERENCES [dbo].[tblSubscription] ([SubscriptionID])GOALTER TABLE [dbo].[tblTransaction] CHECK CONSTRAINT[FK_tblTransaction_tblSubscription]GOtblIssueCREATE TABLE [dbo].[tblIssue]([IssueID] [int] NOT NULL,[JournalID] [int] NOT NULL,[JournalSequence] [int] NOT NULL,[Status] [int] NOT NULL,[DispatchDate] [datetime] NULL,CONSTRAINT [PK_tblIssue] PRIMARY KEY CLUSTERED([IssueID] ASC)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY]ALTER TABLE [dbo].[tblIssue] WITH NOCHECK ADD CONSTRAINT[FK_tblIssue_tblJournal] FOREIGN KEY([JournalID])REFERENCES [dbo].[tblJournal] ([JournalID])GOALTER TABLE [dbo].[tblIssue] CHECK CONSTRAINT [FK_tblIssue_tblJournal]A subscription is to one individual journal and consists of one ormore transactions, and a transaction covers a period of time, say ayear.If there are 6 issues of this journal per year then a 2 yearsubscription might consist of 2 transactions for 1 year each, so forexampleYear 1: Issue13 - Issue18Year 2: Issue19 - Issue24However it is possible for a subscription to pause, or lapse, for aperiod of time between two transactions and miss some issues, forexampleYear 1: Issue11 - Issue16Year 2: Issue19 - Issue24tblIssue is not linked to tblTransaction by any foreign keys, andissues are referenced by JournalSequence number not IssueID, i.e. forYear 2 in the second example above, tblTransaction.IssueStart contains'19' and tblTransaction.IssueEnd contains '24'. Issues are not addedto tblIssue until they are current, so the Issue in tblIssue with thehighest JournalSequence number is the current one (i.e. SELECTMAX(JournalSequence) FROM tblIssue will select the current issue)Journal ID is an integer and will be passed into the SQL statement asa parameter, i.e. @JournalID = 1013What I need is to be able to determine the number of subscriptions(and also the total number of copies for those subscriptions) that arereturning with the current issue (e.g. Issue19 in the examples above)after a lapsed period (the second example), EXCLUDING any that haven'tlapsed, i.e. that have continued straight on (the first example) forany particular journal.I currently have (this returns no results, although there should besome):(Apologies for the tabs, they appear to have gone a bit crazy)SELECTCOUNT(tblSubscription.SubscriptionID) AS NoSubs,SUM(tblTransaction.Copies) AS NoCopiesFROMtblSubscription INNER JOIN tblTransaction ONtblSubscription.SubscriptionID = tblTransaction.SubscriptionIDWHERE(tblSubscription.JournalID = @JournalID) AND(tblTransaction.IssueStart =(SELECTMAX(JournalSequence) AS Expr1FROMtblIssue AS tblIssue_1WHERE(JournalID = @JournalID))) AND(tblTransaction.TransactionTypeID = 11) AND((SELECTMAX(Transactions.IssueStart) AS RestartIssueFROMtblSubscription AS Subscriptions INNER JOIN tblTransaction ASTransactions ON Subscriptions.SubscriptionID =Transactions.SubscriptionIDWHERE(Subscriptions.JournalID = @JournalID) AND(Subscriptions.SubscriptionID = tblSubscription.SubscriptionID)) >1 +(SELECTMIN(IssueEnd) AS ExpiredIssueFROMSELECTTOP (2) IssueEndFROM(SELECTTransactions.IssueEndFROMtblSubscription AS Subscriptions INNER JOIN tblTransaction ASTransactions ON Subscriptions.SubscriptionID =Transactions.SubscriptionIDWHERE(Subscriptions.JournalID = @JournalID) AND(Subscriptions.SubscriptionID =tblSubscription.SubscriptionID))AS derivedtbl_2-AS derivedtbl_1))
View 1 Replies
View Related
Aug 3, 2000
I have a select statement which has to convert some date fields stored
as varchar based on critera of adding numbers to the date. What I have below
will return data show below but gives me an error message when it encounters
any data with null in the field. Is there something wrong in my conversion?
Thanks
declare @tdate varchar(10)
declare @rldate datetime
select @tdate = 20000630
select @rldate = @rldate + 2
select patient_.df_admit_date, patient_.df_ppdb_date from patient_
where (convert(datetime, df_ppdb_date, 101)+ 12) >= @tdate or
(convert(datetime, df_admit_date, 101) + 2 ) >= @tdate
19980910 20000626
19981215 20000627
19590114 20000621
20000629 NULL
20000706 NULL
20000711 NULL
(6 row(s) affected)
Server: Msg 242, Level 16, State 3, Line 5
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
View 1 Replies
View Related
Jul 21, 2004
I need to list all the records in Table2 which don't have matching field values in Table1.
This the the exact opposite of what I need:
SELECT DISTINCT
Field1,
Field2,
Field3,
Field4,
Field5
FROM
[Table1]
WHERE EXISTS(
SELECT DISTINCT
FieldA,
FieldB,
FieldC,
FieldD,
FieldE
FROM
[Table2]
)
The above seems to give me all records in Table1 in which the five fields match the five fields specified in Table2. What does not show up is the test record I put in Table2 which is not in Table1.
What I need, however, is the exact opposite.
I tried the above using NOT EXISTS but I get no records at all.
How do do this?
View 6 Replies
View Related
Jan 11, 2007
Hey all,
I want to run a query that returns the count of records returned by two other queries. Having much trouble with this... I'm sure it's just a triviality. Thanks in advance...
View 2 Replies
View Related
Jul 30, 2014
I have a table named pop. This table has 2 fields, field codes with articles and other field with details of those articles. I intend to make a count to items that have more than 1 detail. In addition to this result is possible result for example:
Detailarticles
64
535
4111
3523
22207
the first line says I have 4 articles with 6 details.
View 16 Replies
View Related
Mar 15, 2004
I am trying to do this in MS Access actually but it is an SQL question.
This query:
SELECT LoanNo, COUNT(LoanNo) AS 'Count'
FROM DedupTest031504
GROUP BY LoanNo
HAVING COUNT(LoanNo) > 1;
returns:
LoanNo Count
46690128 2
46861821 2
47762138 3
47762154 3
48257239 2
48257663 2
48257719 2
48258143 2
48258167 2
which is correct but how do you SUM the COUNT field? In other words, I want the total number of duplicate records in the table. Is there another way alltogether?
I tried:
SELECT LoanNo, SUM(COUNT(LoanNo))
FROM DedupTest031504
GROUP BY LoanNo
HAVING COUNT(LoanNo) > 1;
but I get a "CANNOT HAVE AGGREGATE FUNCTION IN 'SUM(COUNT(LoanNo))' error.
Thanks.
ddave
View 12 Replies
View Related
Apr 21, 2004
If I am totaling fields by groups of rows and I do so for every group do I need to use a stored procedure or cursor for this? I don't have a lot of experience with these areas but will give it a go based on what I find out.
Let me try to provide an example.
BranchNo OrderNo ErrorCode1 ErrorCode2 ErrorCode3
478 111 0 1 1
478 112 0 0 0
478 113 1 0 0
610 119 0 0 0
610 120 1 0 0
I am trying to total the "error code" fields for each Branch. Of course, some don't have any, some have multiple errors. If a stored procedure is the only way, it will be a problem as our company's DBA has not given me permissions to run SPROCs. Is there a way to do this in a query?
I have been trying to figure out a subquery for this and it is not working.
ddave
View 14 Replies
View Related
Sep 6, 2005
Hi all,I'm running into a road block, and I know I've done this before. I'mgetting fields from two tables, and I need to do a count of similaritems with it showing some extra info.Here's my fields:Log.LogId - IntLog.LogDispatcherID - IntOfficer.OfficerID - IntOfficer.OfficerFirstName - VarcharOfficer.OfficerLastName - VarcharI can get the info I need without a count with this:select a.LogID,a.LogDispatcherID,b.OfficerFirstname + ' ' + b.OfficerLastname as OfficerNamefrom[Log] a, Officer bwhere a.LogAssigned1 = b.OfficerIDBut when I try to add a count and group-by it errors out:select Count(a.LogID) as LogCount,a.LogDispatcherID,b.OfficerFirstname + ' ' + b.OfficerLastname as OfficerNamefrom[Log] a, Officer bwhere a.LogAssigned1 = b.OfficerIDGroup By a.LogIDI've done this before, but this isn't working. It's giving the error"it is not contained in either an aggregate function or the GROUP BYclause" for each field other then LogID.How can I do this? I want output similar to this:LogCountLogDispatchIDOfficerName334Tom Jones422John Smith.... EtcThanks for any suggestions or ideas...Sam Alex
View 2 Replies
View Related
Feb 20, 2008
How can I create a Table whose one field will be 'tableid INT IDENTITY(1,1)' and other fields will be the fields from the table "ashu".
can this be possible in SQL Server without explicitly writing the"ashu" table's fields name.
View 8 Replies
View Related
Jul 27, 2006
Hello folks,
I am stuck at a problem, not sure on how to go about writing a query that will return as a percentage the number of fields in a row that are null.
For instance, a row from my table:
Row1 : field1 field2 field3
If field3 is empty or null, my query should return 67%.
So far I have gotten the number of fields:
select count(1) from information_schema.columns where table_name='myTable'
I could loop through the fields but I am sure there is a simpler way of doing it, I have seen something simpler in the past with some builtin SQL functions. I am using MS SQL 2005.
Thanks for your help
Mike
View 2 Replies
View Related
Sep 15, 2015
Trying to get the max count grouped by all the fields. All the fields are the same, but trying to get the location for each physician that has the largest number of patients.
if the output for the sql below is:
101, 10, Jon, Smith, MD, Ortho, OR, 15
101, 10, Jon, Smith, MD Ortho, 1, 12
101, 10, Jon, Smith, MD, Ortho, 2, 10
24, 3, Mike, Jones, MD, Neuro, OR, 21
24, 3, Mike, Jones, MD, Neuro, 2, 43
I'd like to have the query rewritten so the results are as:
101, 10, Jon, Smith, MD, Ortho, OR, 15
24, 3, Mike, Jones, MD, Neuro, 2, 43
SELECT
a.attendingmdkey,e.[provider id],e.[first name],e.[last name],e.title,e.specialty,l.locationname,count(a.accountid) as Count
FROM accounts a
left outer join location l on l.locationid=a.locationid
left outer join providers e on e.[ProviderID]=a.attendingmdkey
where a.dischargedate>='2014-12-01' and a.dischargedate<'2015-01-01'
and a.divisioncode in ('1','2','$')
group by a.AttendingMdKey,e.[provider id],e.[first name],e.[last name],e.title,e.Specialty,l.locationname
order by a.AttendingMdKey
View 3 Replies
View Related
Jan 29, 2015
For example in a table with this fields "field1, L1,L3,L100" field2 the count is 3
it would be better to match a number into the like but i thinks it cannot be done in the like so i've to add another condition to ensure all the text after L is a number.
is this the best way to do it?
Select count(*) from Information_Schema.Columns Where Table_Name = @Table
AND column_name like 'L%' and ISNUMERIC(SUBSTRING(column_name,2, len(column_name)-1))=1
View 6 Replies
View Related
Aug 27, 2007
Hi all
I have a customer table with a postcode and a suburb fields and cutomer info which is manually entered by data entry people...
I am trying to compare the entries against a postcode table with the correct postcodes which have fields postcode and suburb and based on the postcode entered in the customer table it should be the same as the suburb in the postcode table, if they are not the same output them to a table for manual checking..How would I go about this
thanks
View 5 Replies
View Related
Jul 31, 2013
I have a list of items in one table and a field (pageName) in another table that may contain one of the aforementioned items somewhere within that field. There is no fixed position within the field where the itemNo may be so I can't just use SUBSTRING(pageName,2,5) in(select itemNo from tblItem).
Logically, it's like I need to combine IN and LIKE: select pageName where pageName LIKE IN %select itemNo from tblitemNo%..LIKE can only handle one comparison string.
View 5 Replies
View Related
May 1, 2014
I would like to compare some values in two columns which are in the same table. I want to check that there are no differences between the values if the ID is Test1 and Test2
Example table
IDValue1Value 2
TEST1HouseTango
TEST2HouseTango
with test as (
select * from ExampleTable where ID= 'TEST'
),
[Code] ....
View 4 Replies
View Related
Mar 18, 2008
Hi guys, i'm sort of stuck.
I have a list of names, all i have to do is check to see if any of them are in a table, if they are they get deleted. The unique field of the table is title and it is what i have. I don't really know how i am meant to do this. I thought of creating a new table, placing my values into it and comparing to the already existing table, but surely their is a simple way?
View 3 Replies
View Related
Oct 18, 2006
Hi all,
Could anyone let me know if there are any Scripts available for comparing the schema differences between two SQL server databases?
Thanks
DBAnalyst
View 2 Replies
View Related
Jul 20, 2005
Hello everyone,Small and (I think) very simple quesiton;-) which makes me creazy.Let's say I have two tables listed below:T1====IDX====134T2===============IDD fk_IDX===============A1A2A4B1B3B4C4D1D2D3D4I would like to select from table T2 all distinct records IDD whichhave all of fk_IDX containded in T1.The select statement should return in this case ONLY:B and Dbecasue:B has 1,3,4andD has 1,2,3,4 so it has this combination 1,3,4 contained in the T1also.I've tried to do that with group by, with having, in and it neverworks (I always became all records which one of them is in this T1table).Maybe some one from you did try something like that, and can give afast answer.I will be very greatfullGreatingsMateusz
View 2 Replies
View Related