Number Of Distinct Farmers In Each Town
Nov 17, 2013
I have a table name farmer data and it has attributes like farmer name, father name, pesticides used, town etc (these attributes would be required for query). also the data type of columns is var char.
I have to write a query to calculate the average no of farmers in each town.
How can I write this query? I have tried this query but its not giving me the count of all the farmers in each town. its not giving me the count of distinct farmers
select town, count (*)
from farmer_data
group by town
View 7 Replies
ADVERTISEMENT
May 12, 2006
I got the following error on my database when I ran DBCC CHECKDB
Server: Msg 8951, Level 16, State 1, Line 1
Table error: Table 'PhoneCall' (ID 254623950). Missing or invalid key in index 'PhoneCall0' (ID 2) for the row:
Server: Msg 8955, Level 16, State 1, Line 1
Data row (1:98383:16) identified by (RID = (1:98383:16) ) has index values (PhoneCallID = 46361).
This error is on a couple of different indexii?? What do I do? Any help will be appreciated. Thank you
View 5 Replies
View Related
Mar 19, 2008
Wondering if there is a way to do this...
I want to select the DISTINCT user name from each record in a table, and then have another field tell me how many records it found..
is there any way to do this with SQL?
SELECT DISTINCT user_Name FROM table ORDER BY user_Name
is what I have now....
need something that will return:
user_Name numRecs
bob 3
fred 6
sam 1
linda 2
View 1 Replies
View Related
Mar 29, 2007
suppose i have aDataset with 11 rows. field1 with 5 rows of aaa, 6 rows of "bbb"
I want's some thing like
field1 rowcount
aaa 5
bbb 6
View 1 Replies
View Related
May 3, 2006
Hi, I have a table that for ease has this data in:R1, R2, R....z---------------------A | 12A | 22A | 30B | 0B | -1B | -3C | 100I want to generate a table for each distinct row in R1, gives a countof all the rows with data correspondingFor the above table I would getA | 3B | 3C | 1Im probably being stupid but cannot see this at the moment... pleasehelp.Thanks
View 3 Replies
View Related
Feb 12, 2015
select SERIALNO, Max(TIME)
from dbo.TASK A
join dbo.Status B
on A.TID=B.TID
where A.ID in ('1111',2222')
and A.TYPE='Pen'
group by B.SERIAL_NO, B.TIME
order by BM.TIME
For this query I may get serial no duplicates but times are unique
For that serial no, I have to find the recent time. But if I use group by, I am getting the wrong no
View 2 Replies
View Related
Aug 26, 2015
I have table A(year int, month int, user varchar(20)), and I am trying to write a view that would show number of distinct users in the last 3 months, last 6 months, last 9 months and last 12 months(all from the most recent year/month)Â in following format:
3 months   6 months   9 months   12  months
number of distinct users     x          y          z            w
View 20 Replies
View Related
Aug 1, 2006
With reason of the enormous effort carried out to visit the Argentineancity of Córdoba, to participate in the Meeting of MERCOSUR, in theclosing of the Summit of the Towns in the historical University ofCórdoba and in the visit to Altagracia, the city where Che lived inits childhood and together to this to attend immediately thecommemoration of the 53 anniversary from the assault to the barracksMoncada and Carlos Manuel of Grasses, July 26, 1953 in the counties ofGranma and Holguín, days and nights of continuous work withoutsleeping hardly gave place that my health that has resisted all thetests, underwent to an extreme stress and it was smashed.This provoked me an intestinal sharp crisis with sustained spill ofblood that forced me to face a surgical complicated operation. All thedetails of this accident of health consist in the x-rays, endoscopiesand filmed materials. The operation forces me to remain several weeksof rest, far from my responsibilities and positions.As our country it is threatened in circumstances like this by theGovernment from the United States, I have made the following decision:1) I delegate with provisional character my functions as FirstSecretary of the Central Committee of Communist Party of Cuba in theSecond Secretary, partner Raúl Castro Ruz.2) I delegate with provisional character my functions as Major in Bossof the Revolutionary heroic Armed Forces in the mentioned partner,General of Army Raúl Castro Ruz.3) I delegate with provisional character my functions as President ofthe Council of State and of the Government of the Republic of Cuba inthe First Vice-president, partner Raúl Castro Ruz.4) I delegate with provisional character my functions as maininstigator of the National and International Program of Public Healthin the Member of the Political Desk and Secretary of Public Health,partner José Ramón Balaguer Goatherd.5) I delegate with provisional character my functions as maininstigator of the National and International Program of Education inthe partners José Ramón Machado Ventura and Esteban Lazo Hernández,Members of the Political Desk.6) I delegate with provisional character my functions as maininstigator of the National Program of the Energy Revolution in Cuba andof collaboration with other countries in this environment in thepartner Carlos Lage Dávila, Member of the Political Desk and Secretaryof the Executive Committee of the Council of Secretaries.The corresponding funds for these three programs, Health, Education andEnergy, they will continue being negotiated and prioritized, like Ihave come making it personally, for the partners Carlos Lage Dávila,Secretary of the Executive Committee of the Council of Secretaries,Francisco Soberón Valdés, Secretary President of the Central Bank ofCuba, and Felipe Pérez Roque, Secretary of External Relationships whothey accompanied me in these managements and they will constitute acommission for that objective.Our Communist glorious Party, supported by the organizations of massesand the whole town, has the mission of assuming the task commended inthis it Proclaims.The meeting Summit of the Movement of Not Aligned Countries, to becarried out among the days 11 and 16 of September, will receive thebiggest attention in the State and the Cuban Nation to take place withthe maximum of brightness in the agreed date.The 80 anniversary of my birthday that so generously thousands ofpersonalities agreed to celebrate the next one August 13, I request toall to postpone it for December 2 of the present year, on the 50anniversary of the Landing of Granma.I request to the Central Committee of Party and the National Assemblyof the Popular Power the firmest support to this it Proclaims.I don't house the smallest doubt that our town and our Revolution willfight until the last drop of blood to defend these and other ideas andmeasures that are necessary to safeguard this historical process.The imperialism will never be able to squash Cuba.The Battle of Ideas will continue ahead.Alive the Homeland!Alive the Revolution!Alive the Socialism!Until Victoria Always!Fidel Castro RuzMajor in Boss First Secretary of Party and President of the StateAdviceAnd of Secretaries of the Republic of Cuba. July 31, 2006 6:22 p.m
View 1 Replies
View Related
May 21, 2014
I have a table with PO#,Days_to_travel, and Days_warehouse fields. I take the distinct Days_in_warehouse values in the table and insert them into a temp table. I want a script that will insert all of the values in the Days_in_warehouse field from the temp table into the Days_in_warehouse_batch row in table 1 by PO# duplicating the PO records until all of the POs have a record per distinct value.
Example:
Temp table: (Contains only one field with all distinct values in table 1)
Days_in_warehouse
20
30
40
Table 1 :
PO# Days_to_travel Days_in_warehouse Days_in_warehouse_batch
1 10 20
2 5 30
3 7 40
Updated Table 1:
PO# Days_to_travel Days_in_warehouse Days_in_warehouse_batch
1 10 20 20
1 10 20 30
1 10 20 40
2 5 30 20
2 5 30 30
2 5 30 40
3 7 40 20
3 7 40 30
3 7 40 40
how can I update Table 1 to get desired results?
View 2 Replies
View Related
Jul 14, 2014
How can i get the total number of each distinct value in the column and write it to another table e.g.
MyTable
Id Fruit
1 Apple
2 Banana
3 Apple
4 Watermelon
5 Banana
6 Watermelon
7 Apple
Result
Fruit Count
Apple 3
Banana 2
Watermelon 2
View 9 Replies
View Related
May 20, 2014
I have a table with PO#,Days_to_travel, and Days_warehouse fields. I take the distinct Days_in_warehouse values in the table and insert them into a temp table. I want a script that will insert all of the values in the Days_in_warehouse field from the temp table into the Days_in_warehouse_batch row in table 1 by PO# duplicating the PO records until all of the POs have a record per distinct value.
Example:
Temp table: (Contains only one field with all distinct values in table 1)
Days_in_warehouse
20
30
40
Table 1 :
PO# Days_to_travel Days_in_warehouse Days_in_warehouse_batch
1 10 20
2 5 30
3 7 40
Updated Table 1:
PO# Days_to_travel Days_in_warehouse Days_in_warehouse_batch
1 10 20 20
1 10 20 30
1 10 20 40
2 5 30 20
2 5 30 30
2 5 30 40
3 7 40 20
3 7 40 30
3 7 40 40
How can I update Table 1 to see desired results?
View 3 Replies
View Related
Jul 6, 2007
Hi, I have the following script segment which is failing:
CREATE TABLE #LatLong (Latitude DECIMAL, Longitude DECIMAL, PRIMARY KEY (Latitude, Longitude))
INSERT INTO #LatLong SELECT DISTINCT Latitude, Longitude FROM RGCcache
When I run it I get the following error: "Violation of PRIMARY KEY constraint 'PK__#LatLong__________7CE3D9D4'. Cannot insert duplicate key in object 'dbo.#LatLong'."
Im not sure how this is failing as when I try creating another table with 2 decimal columns and repeated values, select distinct only returns distinct pairs of values.
The failure may be related to the fact that RGCcache has about 10 million rows, but I can't see why.
Any ideas?
View 2 Replies
View Related
Mar 12, 2007
I need to run a SELECT DISTINCT query acrossmultiple fields, but I need to add another field that is NON-DISTINCTto my record set.Here is my query:SELECT DISTINCT lastname, firstname, middleinitial, address1,address2, city, state, zip, age, genderFROM gpresultsWHERE age>='18' and serviceline not in ('4TH','4E','4W')and financialclass not in ('Z','X') and age not in('1','2','3','4','5','6','7','8','9','0')and (CAST (ADMITDATE AS DATETIME) >= DATEDIFF(day, 60, GETDATE()))ORDER BY zipThis query runs perfect. No problems whatsoever. However, I need toalso include another field called "admitdate" that should be treatedas NON-DISTINCT. How do I add this in to the query?I've tried this but doesn't work:SELECT admitdateFROM (SELECT DISTINCT lastname, firstname, middleinitial, address1,address2, city, state, zip, age, gender from gpresults)WHERE age>='18' and serviceline not in ('4TH','4E','4W')and financialclass not in ('Z','X') and age not in('1','2','3','4','5','6','7','8','9','0')and (CAST (ADMITDATE AS DATETIME) >= DATEDIFF(day, 60, GETDATE()))ORDER BY zipThis has to be simple but I do not know the syntax to accomplishthis.Thanks
View 2 Replies
View Related
May 8, 2006
Hello,
I have written a small asp.net application, which keeps record of the proposals coming from the branch offices of a bank in a tableCREATEd as a TABLE Proposals ( ID smallint identity(7,1), BranchID char(5), Proposal_Date datetime )
This app also calculates the total number of proposals coming from a specific branch in a given date bySELECTing COUNT(BranchID) FROM Proposals WHERE BranchID=@prmBranchID AND Proposal_Date=@prmDateand prints them in a table (my target table).
This target table has as many rows as the result of the "SELECT COUNT( DISTINCT Proposal_Date ) FROM Proposals"and excluding the first column which displays those DISTINCT Proposal_Dates, it also has as many columns as the result of the"SELECT DISTINCT BranchID FROM Proposals". This target table converts the DateTime values ToShortDateString so that we are able to see comfortably which branch office has sent how many proposals in a given day.
So far so good, and everything works fine except one thing:
Certain DateTime values in the Proposals table which are of the same day but of different hours (for ex: 11.11.2005 08:30:45 and11.11.2005 10:45:30) cause some trouble in the target table, where "SELECT COUNT( DISTINCT Proposal_Date ) FROM Proposals" is executed, because (as you might already guess) it displays two identical dates in ShortDateString form, and this doesn't make much sense (i.e. it causes redundant rows)
What I need to do is to get a result like (in a neat fashion :)
"SELECT COUNT( DISTINCT Proposal_Date ) <<DISTINCT ONLY IN THE DAYS AND NOT IN HOURS OR MINUTES OR SECONDS>> FROM Proposals"
So, how to do it in a suitable way?
Thanks in advance.
View 4 Replies
View Related
Oct 15, 2007
I have created a local user on Report Server Computer and the user has the administrative rights.
When i try to connect Report Server (http://xxx.xxx.xxx.xxx/reportserver) with this user's credantials. (ReportServer directory security is set -only- to Basic Authentication. ).
I get the following error.
Reporting Services Error
--------------------------------------------------------------------------------
The number of requests for "XXXServerXXXUser" has exceeded the maximum number allowed for a single user.
--------------------------------------------------------------------------------
SQL Server Reporting Services
Then i try to login using a different user with administrative rights on the machine, i can logon successfully.
The system is up for a month but this problem occured today?!? What could be the problem?!?
View 2 Replies
View Related
Sep 24, 2007
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)
View 1 Replies
View Related
Jun 23, 2015
Got this query and I need the following result;
declare @NumberToCompareTo int
set @NumberToCompareTo = 8
declare @table table
(
number int
)
insert into @tableÂ
select 4
[Code] ....
The query selects 4 and 5 of course. Now what I'm looking for is to retrieve the number less or equal to @NumberToCompareTo, I mean the most immediate less number than the parameter. So in this case 5
View 4 Replies
View Related
Oct 8, 2006
in my sql, i want to change a decimal number to percent format number, just so it is convenient for users. for example there is a decimal number 0.98, i want to change it to 98%, how can i complete it?
thks
View 4 Replies
View Related
Jun 5, 2007
Hi,
I am currently designing a SSIS package to integrate data into a data warehouse fact table. This fact table has about 70 columns among which 17 are foreign keys for dimension tables.
To insert data in that table, I have to make several transformations and lookups. Given the fact that the lookups I have to make are a little complicated, I have about 70 tasks in my Data Flow.
I know it's a lot, but I can't find a way to make it simpler. It seems I really need all these tasks.
Now, the problem is that every new action I try to make on the package takes a lot of time. At design time, everything is very slow. My processor is eavily loaded each time I change a single setting in one of the tasks, and executing the package in debug mode takes for ages. If I take a look at the size of my package file on disk, it's more than 3MB.
Hence my question : Are there any limitations in terms of number of columns or number of tasks that can be processed within a Data Flow ?
If not, then do you have any idea why it's so slow ?
Thanks in advance for any answer.
View 1 Replies
View Related
Feb 19, 2013
I have a large table of customers. I would like to add a column that contains an integer, unique to that customer. The trick is that this file contains many duplicate customers, so I want the duplicates to all have the same number between them.the numbers dont have to be sequential or anything, just like customers having the same one.
View 8 Replies
View Related
Jan 9, 2015
Okay, I've been working on this for a couple of hours with no success. I'm trying to find the number of telephone numbers that are associated with multiple students at different school sites. I've created a temp table that lists all phone numbers that are associated with more than one student. I'm now trying to query that table and count the number of telephone numbers that are associated with more than one site. Essentially, I'm looking for parent/guardians that have students at different sites.
Here's an example of what I'm hoping to accomplish:
*In this example, I'm just trying to get a count of the different/distinct school sites associated with each number. If I can, at the same time, limit it to a count of > 1 (essentially excluding parents with students at the same site), even better :)
===================================
Temp table
===================================
SCHOOL | STU_ID | STU_PHONE
101 | 12345 | 111-222-3333
101 | 23456 | 111-222-3333
102 | 34567 | 111-222-3333
101 | 45678 | 999-888-7777
101 | 56789 | 999-888-7777
101 | 67890 | 555-555-5555
102 | 78901 | 555-555-5555
103 | 89012 | 555-555-5555
==================================
Wanted query results
==================================
STU_PHONE | #Students | UNIQUE_SCHOOLS
111-222-3333 | 3 | 2
999-888-7777 | 2 | 1
555-555-5555 | 3 | 3
View 1 Replies
View Related
Jul 30, 2007
I have a table that has a street number field.
if the user types in a street number of '2' i would like to return all street numbers the begin with 2 (2,20,21, 200, 201,205,2009,...)
how can this be done.
View 10 Replies
View Related
Nov 26, 2006
Hello people,I might sound a little bit crazy, but is there any possibility that youcan incorporate 4^15 (1,073,741,824) tables into a SQL Database?I mean, is it possible at all? There might be a question of whereanyone would want so many tables, but i'm a bioinformatics guy and I'mtrying to deal with genomic sequences and was coming up with a newalgorithm, where the only limit is the number of tables I can put intoa Database.So, can you please advise if its possible to put in so many tables intoa SQL database? Or is the Bekerley DB better?
View 3 Replies
View Related
Jun 20, 2006
1. how to show page number & total page number in report body?
2. how to show total records number?
View 25 Replies
View Related
Dec 20, 2006
I want to format a number like "#,##0.00" in order to handle it in Excel as a number (i.e. compute a sum).
Excel is able to show a number in a specail format and still allow to compute with ...
Thanks in advance,
Peter
View 3 Replies
View Related
Nov 26, 2006
I am testing something in Visual Basic
that talks to a database and I want to
filter results by -> field1 like "###".
However, that 'like' and '#' is VB syntax.
How do you say that in SQL?
View 7 Replies
View Related
Sep 27, 2007
If I know this SELECT will get me unique username to configname records:Select DISTINCT configname, username FROM EtechModelRequests
JOIN CC_host.dbo.usr_smc ON
username = user_id
JOIN Webservices.dbo.PartNumberPricingImport ON
PartNumber = configname
Where RequestDateTime > '9/26/2007' And country = 'US' And interfacename Like '%download%' And result = 0
How do I show the other fields I need? The field I need is List Price but I don't want to DISTINCT on it too.
View 2 Replies
View Related
Mar 31, 2008
Hi I have two tables linked by MemberID and I'll like to produce a list from the two tables but also want to specify one table to only use the Top 50 distinct records
Table 1 (top 50 Distinct)
Memberid (distinct)PictureDateTakenGalleryID
Order By DateTaken
Table 2
MemberidFirstNameLastNamePrivatelist
MemberidPictureFirstNameLastNameGalleryIDPrivate
View 6 Replies
View Related
Apr 2, 2004
I have a query and it is bringing up multiples of the same data
what i would like to know is if there is a way to use something like Distinct that i can use as a clause
such as
Select *
from Table
Where Distinct ColumnName
I know that distinct doesnt work in this situation
I would like to know if there is a command to do this or a way to fix it
if i use this
select distinct columnName,columnName2
from table
it returns the rows where columnName and ColumnName2 both are not the same
My purpose is that i need to select more than one column but i would like non duplicates based on ONE column name
thanks for any help
View 20 Replies
View Related
May 31, 2002
I have 6 fields in my table and I want to display only distinct values from one of those columns. However, I also need to use the fields from the remaining 5 columns in my asp page. For example,
Columns:
CompID
CompanyName
Ticker
Industry
CEOName
MarketCap
In one table on the page, I want to display only unique entries for 'CompanyName' which I've been doing with this statement:
SqlText = "SELECT DISTINCT CompanyName FROM [tablename] WHERE UserID=" & intUserID & ""
However, I also need to use the other values associated 'CompanyName' in my asp page after opening my recordset. I need to display <%=Rs("CompID"%>, <%=Rs("Ticker"%>, <%=Rs("Industry"%> etc. while maintaining the DISTINCT portion of my statement.
Thanks again.
View 1 Replies
View Related
Sep 12, 2006
I have 3 records in a db..
Table
id
file_name
category
Records
1
bob.jpg
male
2
rob.jpg
male
3
pam.jpg
female
I am trying to grab the distinct category so only 'male and female' is output, but i am also needing to grab the id, file_name to use later on the page as well.
When i try
select distinct category, id, file_name
from tbl_pictures
it outputs all the records since there is no exact match in all 3 fields,
but i am wanting this to happen
select distinct category
from tbl_pictures
but i am still needing to grab the other two fields because i need to use them in the next part of the page
how can i go about doing this
View 1 Replies
View Related
Apr 22, 2008
What is the difference between distinct and group by please?
View 4 Replies
View Related
Apr 24, 2008
Hi,
I have data in several tables and Im having trouble filtering the data.
This is statement that Im executing:
1. SELECT DISTINCT Countries.Name, Companies.ShortName, Persons.FirstName, Persons.LastName, PersonSkills.Skills
This is the statement that gives me the results I want:
2. SELECT DISTINCT Countries.Name, Companies.ShortName, Persons.FirstName, Persons.LastName
The problem with this is I need to have Persons.Skills in the statement and DISTINCT doesnt filter the data the way I need it to because the data in Persons.Skills is different which results in I get duplicated results.
Is it possible to do something like
SELECT DISTINCT column1,..,column5,(SELECT Persons.Skills)
by this I mean to apply distinct to some of the columns in the SELECT statement?
View 6 Replies
View Related
