aggregate-functions

MySQL query problem on group by and max

My table structure is (id,cluster,qid,priority). I'm trying to figure out how I can display the maximum value of priority for each cluster. Say cluster 1 has priorities 100, 102, 105. I want to display the record containing 105. Please help. ...

Fetch Products Grouped By Total Sales ?

Hi, I have the following MySQL tables: TABLE: Products ---------------------- id | productname 1030 | xBox 360 1031 | PlayStation 3 1032 | iPod Touche TABLE: Sales ---------------------- productid | saledate 1031 | 2010-06-14 06:30:12 1031 | 2010-06-14 08:54:38 1030 | 2010-06-14 08:58:10 1032 | 20...

Subquery with multiple results combined into a single field?

Assume I have these tables, from which i need to display search results in a browser: Table: Containers id | name 1 Big Box 2 Grocery Bag 3 Envelope 4 Zip Lock Table: Sale id | date | containerid 1 20100101 1 2 20100102 2 3 20091201 3 4 20091115 4 Table: Items id | name ...

SQL - Count grouped entries and then get the max values grouped by date

I've got a sqlite table holding every played track in a row with played date/time Now I will count the plays of all artists, grouped by day and then find the artist with the max playcount per day. I used this Query SELECT COUNT(ARTISTID) AS artistcount, ARTIST AS artistname, strftime('%Y-%m-%d', playtime) AS day_played FRO...

How to count NULL values in MySQL?

I want to know how can i find all the values that are NULL in the MySQL database for example I'm trying to display all the users who don't have an average yet. Here is the MySQL code. SELECT COUNT(average) as num FROM users WHERE user_id = '$user_id' AND average IS_NULL ...

Easy SQL Group-By question. (I'm new)

I am new to SQL so hopefully this has an easy answer. I have a Students table (studentID, name, statusID) and a StudentsClasses table (studentID, classID). I've been asked to create a view from the above tables that returns the following columns: classID: (group-by) count of students in each class AS Students count of students w...

mysql update table from another table

Hi, I'm trying to update a field in one table, from the sum of another field, in another table. company_tbl (PRIMARY, companySize, companyName) location_tbl (PRIMARY, companyID, locationSize, locationName) The two tables link by company_tbl.PRIMARY = location_tbl.companyID update company_tbl comp, location_tbl loc set companySize = su...

JOIN on another table after GROUP BY and COUNT

I'm trying to make sense of the right way to use JOIN, COUNT(*), and GROUP BY to do a pretty simple query. I've actually gotten it to work (see below) but from what I've read, I'm using an extra GROUP BY that I shouldn't be. (Note: The problem below isn't my actual problem (which deals with more complicated tables), but I've tried to c...

How and why combination of JOIN and subquery affects GROUP BY behavior in MySQL query?

I have 3 sql tables: Data36 (Data_ID:int <PK>, type:int), Data38(Data_ID:int <PK>, clientId:int), Data47(Data_ID:int <PK>, payerID:int). I thought the following queries are identical, because I don't use aggregate functions here and GROUP BY should behave the same way as DISTINCT. But they return very different result sets and I do...

Select distinct values from two columns

I have a table with the following structure: itemId | direction | uid | created 133 0 17 1268497139 432 1 140 1268497423 133 0 17 1268498130 133 1 17 1268501451 I need to select distinct values for two columns - itemId and direction, so the output would be lik...

List number of rows per field value in SQL

Hi, database/SQL novice here. I have a table with a column called, for example, "EmployeeID". I want a query that will, for each distinct employeeID, return the number of rows that have that as their ID. I hope it's clear what I'm trying to do and someone will know how to help! I don't think it should matter, but just in case, I'm usin...

SQL query that applies aggregate function over another aggregate function

I have several query results that use one or more aggregate functions and a date GROUP-BY so they look something like this: Date VisitCount(COUNT) TotalBilling(SUM) 1/1/10 234 15765.21 1/2/10 321 23146.27 1/3/10 289 19436.51 The simplified SQL for the above is: SELECT ...

mysql count help

I am trying to count the number of rows in a table that have an area, how can I achieve this, I currently have this query written, SELECT DISTINCT area FROM cv WHERE status = 'SHOW' AND is_complete = 'TRUE' ORDER BY area ASC This query currently returns, area ------------------ West Yorkshire Lanchashire What I am w...

Cumulative sum over days

I have a MySQL table like the following: date count 2010-01-01 5 2010-01-02 6 2010-01-03 7 How can I accumulate the sum of each day to the next one? So the result is like: date acum per day 2010-01-01 5 2010-01-02 11 2010-01-03 18 I think i need some kind of for(each date)... but no clue. Just the fin...

MAX() and MAX() OVER PARTITION BY produces error 3504 in Teradata Query

I am trying to produce a results table with the last completed course date for each course code, as well as the last completed course code overall for each employee. Below is my query: SELECT employee_number, MAX(course_completion_date) OVER (PARTITION BY course_code) AS max_course_date, MAX(course_completion_d...

Correcting the SQL syntax?

Hi, Thanks very much for those responded. Is there a way in SQL server that takes the data from table1 and outputs the data like table2? Thanks! Table1: +---------+-----------+----------+------------------+ | Name | DOB | Agent ID | Agent Name | +---------+-----------+----------+------------------+ | subject | 4/20/196...

MySQL Distinct Records With Count

I have a photos table, with two columns in that table named "id" and "user_id". Obviously one user can have many photos. I'd like to run a query that can give me the photo count for each user. Any help is appreciated. ...

T-SQL 2005: Counting All Rows and Rows Meeting Criteria

Here's the scenario: I have a table with 3 columns: 'KeyColumn', 'SubKeyColumn' and 'BooleanColumn', where the first two are the primary keys of the table. For my query, I'd like to count the number of rows there are for any given value in 'KeyColumn', and I'd also like to know which ones have a value of true for 'BooleanColumn'. My i...

Cannot use Max with Count in SQL*Plus

this is my sql statement i get this error. but when i use only Max to single and without displaying other results it works. can someone help me SELECT cat.CategoryName,sb.SubCategoryName,MAX((COUNT(bs.BookID))) FROM Category cat,SubCategory sb, Book_Subcategory bs WHERE cat.CategoryID = sb.CategoryID AND sb.SubCategoryID = bs.SubCat...

Updating SQL table with calculations regarding previous rows

Hello, I'm trying to fix some errors on a big database of stock exchange data. One column (quantity) has the traded volume on each tick, and other column stores the cumulative volume (i.e., the sum of the previous ticks of the day). This second column is wrong in some cases (not a lot, so we can safely assume that no to adjacent ticks a...