mysql - order by frequency with multiple columns
I have a table with 5 columns: tag 1 tag 2 tag 3 tag 4 tag 5 If I want to show results ordered by the popularity(frequency) of those tags, what kind of query would i use? ...
aggregate-functions
SQL query to get group by and distinct values at the same time
Hello, I'm having trouble trying to define the SQL query for this table: There's a table of patients and their weight readings recorded on visits with the following columns: patient ID weight reading visit ID (one per visit) In other words, if in two records two visit IDs are the same, then two weight readings have been taken on ...
Want to sum some fields and leave others alone SQL
I have a query that results in a field of varying lengths (6, 8, 10, ...) and respective counts as seen below: "region","repeatLength","count" "promoter","6","272387" "promoter","8","86929" "promoter","10","28337" "promoter","12","8873" "promoter","14","3080" "promoter","16","1098" "promoter","18","475" "promoter","20","206" "promoter"...
Count Unique Results in T-SQL
My query is: SELECT DISTINCT IncidentStatus.IncidentStatusName, Incident.IncidentID AS Bob FROM Incident INNER JOIN IncidentMember ON Incident.IncidentID = IncidentMember.IncidentId INNER JOIN IncidentStatus ON Incident.IncidentStatusID = IncidentStatus.IncidentStatusID WHERE ...
switch between aggregate functions with linq
I have two methods that look almost the same, except for the aggregate function used in the Linq query. For example: public IEnumerable<Item> DoStuff(IEnumerable<Item> someItems) { var items = someItems.GroupBy(i => i.Date).Select(p => new Item(p.Key, p.Sum(r => r.Value))); // ... } public IEnumerable<Item> DoOtherStuff(IEnumer...
Selecting same column with different where conditions
This query returns the sum of "closed" daily sales for a particular salesperson within a particular date range: SELECT SUM(price) as closed_total FROM dbo.Sales WHERE salesperson_ID = @salesperson_ID AND date_ordered BETWEEN @start_date AND @end_date AND closed = 1 GROUP BY date_ordered The output looks like:...
Using LINQ to SQL group, sum and aggreate all together
Hi, I have two tables Students and Origami. Origami has Foreign Key of Students table. Each student can make one or more origami for each month. Students sample data: StudentId, FirstName, LastName 187 , John , Maslow 196 , Crystal , Hood 195 , Sarah , Lewis Origami sample data: OrigamiId, StudentId, CreationDate, NumberOfOriga...
Need help with a MYSQL query grouping
Table schema is set up something like this: userID Points timestamp 1 40 3 20 1 10 4 15 3 5 Need to be able to present a report that shows the following: Total Points Allocated for the Day (0 if none allocated), (nice to have) To what userID's the points were allocated to ...
SQL Server: Having trouble getting correct decimal value after cast
Hello, I'm trying to write a query that gives me a percentage (i.e. something like .885, for example) by dividing 2 aggregate numbers I selected via SUM. But my results are coming out as 0.0 instead of the correct number. So just for reference, starting with 2 queries, I have: SELECT SUM(CASE WHEN Status_ID = 1 AND State_ID = 14 THEN 1...
SQL Server: How to handle divide by 0 cases in query
Hi all, I have a query that looks like this, and which is getting a percentage: SELECT SUM(convert(decimal(3,2), CASE WHEN Status_ID = 1 AND State_ID = 14 THEN 1 ELSE 0 END))/ SUM(convert(decimal(3,2),CASE WHEN State_ID = 14 THEN 1 ELSE 0 END)) * 100 FROM SomeTable This may return something like 59.77803 in a norm...
SQL to query by date dependencies
Hi, all. I have a table of patients which has the following columns: patient_id, obs_id, obs_date. Obs_id is the ID of a clinical observation (such as weight reading, blood pressure reading....etc), and obs_date is when that observation was taken. Each patient could have several readings on different dates...etc. Currently I have a quer...
SQL query to get single row value from an aggregate
Hi, all. I have an Oracle table with two columns ID and START_DATE, I want to run a query to get the ID of the record with the most recent date, initially i wrote this: select id from (select * from mytable order by start_date desc) where rownum = 1 Is there a more cleaner and efficient way of doing this? I often run into this pat...
help with a sql query joining 2 many-many tables
i want help how to solve this sql problem. suppose i have 3 tables Movie ID Name Genre ID Name Movie_Genre (this one is the link for many to many) FK_MovieID FK_GenreID i want to select all the movies that are of genre 1 and genre 3 how is this possible? i can only select the movies of 1 genre but not the movies...
SQL highscores.
Ok, I have two MYSQL tables: CREATE TABLE `sessions` ( `id` int(11) NOT NULL AUTO_INCREMENT, `userID` int(11) NOT NULL, `runTime` int(11) NOT NULL, `monstersKilled` int(11) NOT NULL, `profit` int(11) NOT NULL, `tasks` int(11) NOT NULL, `xpGain` int(11) NOT NULL, `lastupdate` int(11) NOT NULL, PRIMARY KEY (`id`) ); An...
select in sql server 2005
I have a table follow: ID first end a 1 3 b 3 8 c 8 10 I want to select follow: ID first end a-c 1 10 But i can't do it. Please! help me. Thanks! ...
Counting groups of data in SQL
I have a table of about 1.6M rows where the records have a unique (identity) ID and then also have a 8-character "code" field. The code field it used to group data into sets...all rows with the same code are in the same set. Each set should consist of 12 records, but it seems our data load was off and some sets are incomplete. I need ...
MySQL: Pivot + Counting
I need help with a SQL that will convert this table: =================== | Id | FK | Status| =================== | 1 | A | 100 | | 2 | A | 101 | | 3 | B | 100 | | 4 | B | 101 | | 5 | C | 100 | | 6 | C | 101 | | 7 | A | 102 | | 8 | A | 102 | | 9 | B | 102 | | 10 | B | 102 | =================== to ...
How to get multiple columns in one variable in mysql query
I have the table Articles -------- id,name,type_id Type--------------id,name article_type ----------article_id , type_id I using this query select A.name from types A inner join article_type B on ( B.type_id = A.id and article_id = 10) Now this query is working but it gives me separate rows but i want all types in one variables so...
Avoid to nest aggregation functions in PostgreSQL 8.3.4
Assuming that my subquery yields a number of rows with the columns (x,y), I would like to calculate the value avg(abs(x-mean)/y). where mean effectively is avg(x). select avg(abs(x-avg(x))/y) as incline from subquery fails because I cannot nest aggregation functions. Nor can I think of a way to calculate the mean in a subquery while kee...
MS Query to exclude state abbreviations
I have a query that I would like to exlude US States from. I am trying to find all data related to countries other than the US. I have the abbreviations for all the US States and can either link the table to the query and exlude them somehow or write a statement in the query to exlude them. But, I don't know how to do either. I thought I...