I'm trying to make a query that will check for duplicates, and if I find duplicates I want to be able to update those accordingly. For example, I have the following data set, how do I only select the 2 rows that have the same value for 'position'? ID position 1 0 2 1 3 2 4 1 5 ...
How can I make this query work : SELECT column1.....,SUM(Hits) AS Hits FROM table WHERE SUM(Hits) > 100 GROUP BY column1..... The problem is the where clause, mysql display error : Error Code : 1111 Invalid use of group function I try to change the query to : SELECT column1.....,SUM(Hits) AS Hits FROM table WHERE...
Can someone convert this english to SQL I have tried several things but no luck. SELECT CASE WHEN COUNT(person_id) > 3 THEN person_id end FROM table I am trying to only get the person_id(s) that occur > 3 times in the table. ...
Hi, I have this two tables: mysql> desc vat_rates; +-------------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+---------------+------+-----+---------+-------+ | rate_id | varchar(5) | NO | PRI | NULL | | | name | varchar(255) | ...
I have a SQL Server 2008 database with millions of records. One field has values ranging from 0 to 250 and may, or may not, include all numbers witin the range. How do I query the database to get a list of distinct values and the number of records contaiing that value? I used a Select Count(Distinct) query but that only gives me the num...
Hi all, i need to select A list showing the customer id, title, first name and surname of each customer who has hired a car, sorted alphabetically by customer surname together with a count of the number of bookings each of them has placed. I've done the first part but not sure where to put count for the number of bookings placed. Here...
How would I do a basic profit and loss statement in SQL? I have a sales table with a costs field, and summing that field should show the total profit Likewise I have a purchases table with a cost field, which should show the total money going out. To show profit, would I just SUM the total costs in sales, and subtract the total costs ...
I want to write an efficient query which returns a list of fruits by type, the lowest price for the type of fruit and the name of the fruit. Right now, I have a query which return me the fruit type and the lowest price for that type (see below). But I am unable to get the name of the cheapest fruit. Any idea how I can achieve that? Than...
Hi, sql column - trans_value contain both positive and negative value amount. so i'm trying to figure out how do i set a sum of positive value and negative value aside, so that i can calculate the how much sum of positive and how much is sum of negative. so in the end, i can minus both, positive - negative. edit, i forgot to mention ...
Hi, all! MySQL table is like this (VoteID is PK): VoteID VoteValue CommentID 1 -1 1 2 -1 1 3 1 1 4 -1 1 5 1 2 6 1 2 7 -1 2 I need a result: CommentID Plus Minus 1 1 3 2 2 1 ...
I currently have SELECT * FROM bidders WHERE status='0' AND email IS NOT NULL GROUP BY `bid_user` ORDER BY 'bid_price' DESC The problem that I have is that "bid_user" may exist in different rows with different status ( status=1 or status=0). I would like to know if it's possible to select only the * rows where status=0 and whe...
My Oracle query produces the correct result set, but data is being presented with odd characters as you can see by the blocks in the picture below. Any reason that you can think of as to why it would do this and what these characters actually are? Below is the query I'm using. Thanks in advance. SELECT wmsys.wm_concat(userFirstName) ...
Hello to all! I have a sql statement like this: select a.id, a.valfrom ... inner join ... where ...; As a result I have this: id val --------- 3 10 3 10 3 10 9 21 9 21 11 2 11 2 13 30 So you can see, one id has one value. If I do a group by (a.id), I get: id val --------- 3 1...
Query: SELECT project_id, COUNT(*) AS count, MIN(date_added) AS date_start, MAX(date_added) AS date_end FROM my_table GROUP BY project_id, TIMESTAMPDIFF(MINUTE, date_added) < 5 WHERE user_id = 1 LIMIT 10 How can I accomplish this? I want to group the items so that no two consecutive items in a group...
I want to filter counted query result. select count(distinct tmr_id) AS Count ,CONTRACTID from status_handling This query return 2 columns like: Count ContractID 1 23344 2 28344 2 34343 2 29344 1 26344 I just filter 2 (Count) values. How can I do this? Thanks. ...
suppose I have this table id | cash 1 200 2 301 3 101 4 700 and I want to return the first row in which the sum of all the previous cash is greater than a certain value: So for instance, if I want to return the first row in which the sum of all the previous cash is greater than 500, is should return to row 3 How do I d...
Please help me to write the following sql. I have a table like this, with an amount column and date column amount date ----------------------- 100 - 2010-02-05 200 - 2010-02-05 50 - 2010-02-06 10 - 2010-02-06 10 2010-02-07 what I want is to write a sql to get total for each day. Ultimately my query...
I have a table that tracks serial numbers and the number of licenses associated with said serial number. A client can re-license a box, increasing or decreasing the number of users, and they are only billed for the changed amount. The following table structure exists: id - Auto Incrementing ID serial - The serial number numusers - the ...
This may be a stupid question but is it possible to store a count query in a field in a table such that when the result of the count changes the result will update itself? Thread(ThreadID,NumMessages) Message(MessageID,ThreadID,MessageText,PreviousMessage) I want to update the Thread.NumMessages field any time a message with the c...
I have two tables, one that stores serial number uut_build, and another for measurements that belong to the serial numbers measure_list, I'm trying to get the count of records in measure_list that belongs to a unique serial number. Query #1 select id, uut_sn from uut_build where uut_sn like 'A%'; Query #2 select count(*) from measur...