blyter blyter - 5 months ago 96
MySQL Question

How do I perform a calculation with a distinct ID in MySQL?

Wasn't sure how to ask this question. Basically I have a table:

create table sales (
bookcode int not null,
storecode int not null,
saledate date not null,
quantity int not null,
price decimal,
foreign key (bookcode) references books(bookcode),
foreign key (storecode) references stores(storecode)

and what I'm trying to do here is calculate the revenue for each store code. It will multiply the quantity by the price which is fine but I don't know how to get it so if there are multiple of the same storecode, it groups them together and gives a total amount

For example: if there are two stores in the sales table with the same store code of 1, I want to get the revenue of both and have them show up as a mass total for that storecode, instead of showing them separately. How can I do this?

Answer Source

What you are looking for is a GROUP BY clause, e.g.

SELECT storecode, SUM(quantity * price) AS total_sales
    FROM sales
    GROUP BY storecode;

This clause directs the SQL engine to group together rows which have an identical storecode, then apply aggregate functions to combine the results from each set of rows. In this case, the aggregate function used is SUM(); other common aggregate functions include COUNT(), AVG(), MIN(), MAX(), and GROUP_CONCAT().

Keep in mind that, if you're using a GROUP BY clause, every column you select must be either one of the values that's being GROUPed BY, or an aggregate function. For instance, you could not:

SELECT storecode, saledate FROM sales GROUP BY storecode  /* no! */

because saledate is neither a column that's being grouped by, nor an aggregate function. (To put it simply: there's multiple sale dates, which one is the query engine supposed to give you?)

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download