I would like to know if it's possible to find a value in a select statement and use it in a where clause like: SELECT col1, MAX(col2) - COUNT(DISTINCT col3) as variable FROM table WHERE col1 > variable "table" is pretty big, and I want to narrow down the records the query has to look at as quickly as possible. I know this isn't bit ...
Urgh...my LINQ has suddenly taken a turn for the worse!! I have two tables and want to join and perform aggregate functions upon the tables. Firstly, is this possible, I am assuming it is, and secondly, how do I handle the aggregate functions? As part of the same LINQ statement? The SQL statement will look a bit like this: SELECT ...
Hello, I have a table (PAT_PROCEDURES) with three columns: patient_id, procedure_id, procedure_date, and token_id that stores records about patients and procedures they've undergone as well as the procedure's date; token ID is a special numeric identifier. I also have another table (PAT_TOKENS) with three columns patient_id and token_...
Hi, I have a list of grades that students received. Each student appears multiple times in the table. How do I produce a list of all the students with their average grade? P.s. I've tried looking at previously asked questions to see if I can find something relevant, but with no luck. ...
There is query which is asking for favourite products which is bought by each coustomer. i have to select and in the first select i have selected the count of products that each customer bought. in the other select i want to select the maximum of that boughts for each customer.but when i want to select max(previous select column) it gets...
dear all..i have some data at DB. name Range bla 123x9901-123x0000 //it means range 9901 until 10000 = 100 bla 123v0001-123v0100 // 10001-10100 = 100 i want the result like: name Qty bla 200 i counting them use: SELECT................ IF(RIGHT(Range,4) = "0000",10000,RIGHT(Ra...
Hi. I have table: CREATE TABLE [dbo].[test] ( [name] nvarchar(max) NULL, [date] datetime NULL ) And records on it: a 2010-09-02 12:00:00 a 2010-09-02 11:00:00 b 2010-09-02 12:00:00 b 2010-09-02 11:00:00 And i want to get all name with the newest date: I may do: select t.[name] from test t group by t.[name] having max(date)...
i have a table with two column: Name Values ---------------- Target 20 v1 10 v2 20 v3 10 Total 30 percent ? i want to calculate the percentage with the single column value to get the formula as --> Target/Total * 100. i.e for ex: Total = SUM (Values) in that way...... by using two rows in the colu...
I have a troublesome MySQL query as follows: SELECT camera_id, ((avg(low_price) + avg(high_price)) / 2) as avg_price FROM camera_general, camera_products WHERE camera_id = ir_camera_id AND dp_post_dt IS NOT NULL AND dp_post_dt NOT LIKE '0000%' AND currently_manufactured = 'Yes' AND ((no_of_sellers >= 0) OR ((TO_DAYS(CURRENT_DATE) - TO_D...
I have this query below. There are 4 main tables involved: tblOrder, tblItems, tblOrder_archive, tblItem_archive. Orders and Items get moved over to the archived versions of the tables after a few months as not to slow down the main table queries. (sales and traffic is REALLY HIGH). So to get sales figures, i select what i need from each...
I have 2 tables - sentiment & comments - that I'm trying to join averages and counts from based on a set of derived time ranges in mysql. Here's what I have that works individually: SELECT ROUND(AVG(sentiment.sent_value)) AS sentiment, ROUND( FLOOR( (sentiment.sent_video_time) /5000 ) *5000 ) AS start_time, ' - ', ...
Hello all I have the following LINQ conditional where clause query that produces a result of weights: From this, I'd like to take the result set and join on another table, tblPurchases var result = weights.Join(getsuppliersproducts.tblPurchases, w => new { w.MemberId, w.MemberName, w.LocationId, w.UnitId }, p => new { p.M...
I have to tables, keywords and data. Table keywords have 2 columns (id, keyword), table data have 3 columns (id[foreign key of keywords.id], name, value). I am using this query: SELECT k.id, d.value, d.name FROM keywords AS k INNER JOIN data as d ON k.id = d.id it returns something like: 1 123 name1 1 456 name2 2 943 name1 3 542 n...
Hello, I Have a table with the following columns: patient_id, visit_id, and visit_date. How can I write a query to get the max(visit_id) on the most recent visit date for each patient? (several visit_id could occur on the same date for the same patient) basically, I want to end up with NO duplicate patient ID's. Thanks. ...
I need to do the same group by on a bunch of different aggregates that I'm getting with nested subqueries in Postgresql 8.3. If I do this: select f10 as report_id, (SELECT AVG(age) FROM (select f10 as report_id, f62 as age from reports where f55 in ('1')) ...
First of all I am using Oracle 10g Express So there are three columns I want to select: [domain_name] [index_path] [collection_name] Now there are two columns that I want to be unique (as a group): [domain_name] [index_path] And then I want to select the row baised on when another column [gen_timestamp] is most recent. So my issue ...
Say I have this data set user | group --------+------- [email protected] | A [email protected] | B [email protected] | A [email protected] | B [email protected] | A [email protected] | B [email protected] | C I want to convert this into a table like this: user | IN_A | IN_B | IN_C --------+-------+-------+------- [email protected] | TRUE | TRUE | FALSE [email protected] | TRUE | FALSE | FALSE c@...
Suppose I have three tables: user, group and xref, a table that gives them many-to-many RI. I might want to see how groups each user belongs to: select user.user_id, user.user_name, count(*) as group_count from user inner join xref on user.user_id = xref.user_id inner join group on group.group_id = xref....
How do I write the below with ICriteria and Projections.Sum: Select item_name, sum(unit_price * amount) from sales group by item_name Thanks ...
So usually you can just do SELECT COUNT(field.id) FROM table WHERE field.id > 100 and COUNT(field) will return the number of entries that has the criterion of field.id > 100 But then what if you what to count entries specified with the HAVING criterion such as SELECT COUNT(field.id), field.id * 10 AS foo FROM table HAVING foo > 100...