aggregate-functions

COUNT and GROUP BY on text fields seems slow

I'm building a MySQL database which contains entries about special substrings of DNA in species of yeast. My table looks like this: +--------------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+---------+------+-----+---------+-------+ | species | text | YES ...

MySQL Join Query

I need to query the database by joining two tables. Here is what I have: Table Town: id name region Table Supplier: id name town_id I currently have the following query which outputs all the Towns that belong to a given region: SELECT id, name FROM Town WHERE region = 'North West'; Now I need to extend this query and create t...

problem using aggregate functions with multipul table joins

My goal is to make an aggregate function (sum) and group the elements , but there is an error this is all the steps that i have done 1- first step code SELECT ca.question_id , ca.choice_0 ,ca.choice_1 ,ca.choice_2 ,ca.choice_3 ,ca.choice_4 ,q.headline_id FROM closed_answers ca INNER JOIN questions q ON ca.question_id...

Problem with MySQL query for high scores leaderboard

Hello, I have a MySQL high scores table for a game that shows the daily high score for each of the past days of the year. Right now I am doing a PHP for-loop and making a separate query for each day, but the table is becoming too large to do that so I would like to condense it into one simple MySQL statement. Here is my new query right...

When I read other posts about this, I still can't see the difference in SQL between GROUP BY and ORDER BY. What is it?

I've started my own thread on this question so as to have less overhead from posting it on someone else's thread. I have trouble understanding in SQL what the difference is between GROUP BY and ORDER BY. I know there have been threads made about this, but they don't provide me with a useful answer. Here is something along the general ...

Aggregate functions return wrong values when joining more tables

I want to display all customers and their addresses and the number and total sum of their orders. My query looks like this: select *, sum(o.tota), count(o.total) from customer c natural join orders o group by c.custId; which works fine. but if I add a new table to the query: select *, sum(o.tota), count(o.total) from customer c ...

I'm having problem with SUM() in MySQL

Hi All, I want to fetch the BalanceAmt of a particular Customer attended by a particular attender. The criteria is that a customer may buy more than one items. Each bought items has been recorded individually. At some cases 'attender' value would be NULL. Here's my query: SELECT Customer, AttendedBy, SUM(BalanceAmt) FROM billing GROU...

MySQL joins with WHERE clause

I have 2 queries which display 2 different values but the results from one query are unexpected. Query1: SELECT SUM(T.amount_reward_user) AS total_grouping FROM fem_user_cards UC LEFT JOIN fem_transactions T USING(card_number) LEFT JOIN fem_company_login FCL ON T.fem_company_login_id=FCL.fem_co...

MySQL Query - SELECT (average of a category) AS "CATEGORY AVERAGE"

Objective: When user browses to a particular seller, then display his average along with the average of sellers from similar category for easy comparison. Example Data: Seller | Category | Qty | Sales -------------------------------------------- Harry | Mango | 100 | 50000 John | Apple | 75 | 50500 Max | Ma...

MySQL Join Query (possible two inner joins)

I currently have the following: Table Town: id name region Table Supplier: id name town_id The below query returns the number of suppliers for each town: SELECT t.id, t.name, count(s.id) as NumSupplier FROM Town t INNER JOIN Suppliers s ON s.town_id = t.id GROUP BY t.id, t.name I now wish to introduce another table in to th...

MySQL Combine multiple rows

I have a table similar to the following (of course with more rows and fields): category_id | client_id | date | step 1 1 2009-12-15 first_step 1 1 2010-02-03 last_step 1 2 2009-04-05 first_step 1 2 2009-08-07 last_step 2 ...

MYSQL why would min be larger than max?

select min(lead), max(lead) from products WHERE part_line != 90 and category = 'x' When I run the above query the min returned is larger than the max. If I remove the condition pline != 90 the problem goes away. I was thinking at first that mysql was processing the min, max before it got to pline... but that doesn't explain why it w...

How to create a list of seconds in MySQL

I have the following query: SELECT timestamp, COUNT(*) FROM table GROUP BY timestamp But some timestamps do not show up because there is no data. Here's an example 1:00:00 | 3 1:00:02 | 17 1:00:03 | 2 Notice that 1:00:01 is missing. Is there a way to make the 1:00:01 | 0 appear in the result? ...

Aggregating values into a table type within a GROUP BY query

Suppose you have a table (in Oracle): CREATE TABLE CUSTOMER ( customer_id NUMBER, gender CHAR(1) ); And suppose you have a table type: CREATE TYPE NUMBER_TABLE_TYPE AS TABLE OF NUMBER; Is it possible to write a GROUP BY query such that, for each group, that group's primary key fields are stored in a NUMBER_TABLE_TYPE? For ...

Retrieving alternate attribute values in GROUP BY query?

Let me explain what I mean with that question: Lets say I have to tables like these: customers id customer location 1 Adam UK 2 Pete US values id value 1 10 1 7 2 3 2 41 Let's ignore here for a moment that that (and the following query) wouldn't make a lot of sense. It's meant as a simplified example. Now, if ...

Grouped string aggregation / LISTAGG for SQL Server

Hi, I'm sure this has been asked but I can't quite find the right search terms. Given a schema like this: | CarMakeID | CarMake ------------------------ | 1 | SuperCars | 2 | MehCars | CarMakeID | CarModelID | CarModel ----------------------------------------- | 1 | 1 | Zoom | 2 | 1 ...

MYSQL select count of rows that fall in a month for every month of the year.

With the table: id date_from date_to --------------------------- 1 2010-01-01 2010-03-01 2 2010-02-07 2010-05-01 3 2010-07-05 2010-07-10 I am trying to return a result which has one row for each month of the year that lets me know how many rows were active in that period. So for the above I would want the result ...

How do I filter this MySQL query to only include items not rated by a specific user?

Given this sample dataset: item ---- item_id item_name item_added 1 Apple <date_time> 2 Banana <date_time> user ---- user_id user_name 1 Alice 2 Bob 3 Carol rating ------ rating_id item_id user_id rating_value 1 1 1 3 2 1 2 4 3 1 ...

Preferred syntax for adding SQL SUMs together

I want to return the total sales. That is to say the UnitPrice + TaxAmount for the entire set of data. I can write the SQL query two different ways with the same result returned. SELECT SUM(UnitPrice + TaxAmount) AS 'TotalSales' FROM Sales or SELECT SUM(UnitPrice) + SUM(TaxAmount) AS 'TotalSales' FROM Sales Is one of these que...

How to combine aggregate functions in MySQL?

I'm just learning MySQL - is there a way to combine (or nest) aggregate functions? Given a query: SELECT user, count(answer) FROM surveyValues WHERE study='a1' GROUP BY user; This will give me the number of questions answered by each user. What I really want is the average number of questions answered per user...something like: SEL...