Mysql: Group by Two Columns and Sum

MySQL: Group by two columns and sum

Based on your example table, it appears you want to be grouping on product rather than id. You merely need to add the Size column to both the SELECT list and the GROUP BY

$query = "SELECT 
            product,
            Size, 
            SUM(Quantity) AS TotalQuantity 
          FROM inventory
          GROUP BY product, Size";

Note that I have added a column alias TotalQuantity, which will allow you to more easily retrieve the column from the fetched row via the more sensible $row['TotalQuantity'], rather than $row['SUM(Quantity)']

MySQL group by with multiple column sums and a total sum for each group

Try,

SELECT person, 
       sum(positive_vote) totalPositive, 
       sum(negative_vote) totalNegative,
       (sum(positive_vote) + sum(negative_vote)) totalVotes
FROM Votes 
GROUP BY person
-- HAVING (sum(positive_vote) + sum(negative_vote)) < 5

MySQL - Use GROUP BY and SUM to multiply two columns

As noted by patrick3853 and Olivier Depriester in the comments, the line SUM(jo.quantity * jo.price) should instead be SUM(jo.quantity) * SUM(jo.price).

The correct query is as follows:

SELECT p.name AS 'Project Name',
       SUM(jo.quantity) AS 'Job Order Quantity',
       SUM(jo.price) AS 'Job Order Price',
       SUM(jo.quantity) * SUM(jo.price) AS 'Cost'
FROM projects p
JOIN job_orders jo ON p.id = jo.project_id
GROUP BY  name;

MySQL Sum total using Group By multiple columns

The reason you get 3 records is that the group by contains denomination. If we change the logic so that we sum instead of count(*) * denomination then we can get the two records your after. (provided you remove denomination from the group by)... but maybe i'm missing something.

SELECT `brand`,
       Sum(`denomination`) AS 'total'
FROM `inventory`
WHERE `owner` = 'owner-one'
  AND `currency` = 'GBP'
  AND `activated_at` IS NULL
  AND `expires_at` >= '2018-06-21'
GROUP BY `owner`,
         `brand`,
         `currency`

Using group by on multiple columns

Group By X means put all those with the same value for X in the one group.

Group By X, Y means put all those with the same values for both X and Y in the one group.

To illustrate using an example, let's say we have the following table, to do with who is attending what subject at a university:

Table: Subject_Selection

+---------+----------+----------+
| Subject | Semester | Attendee |
+---------+----------+----------+
| ITB001  |        1 | John     |
| ITB001  |        1 | Bob      |
| ITB001  |        1 | Mickey   |
| ITB001  |        2 | Jenny    |
| ITB001  |        2 | James    |
| MKB114  |        1 | John     |
| MKB114  |        1 | Erica    |
+---------+----------+----------+

When you use a group by on the subject column only; say:

select Subject, Count(*)
from Subject_Selection
group by Subject

You will get something like:

+---------+-------+
| Subject | Count |
+---------+-------+
| ITB001  |     5 |
| MKB114  |     2 |
+---------+-------+

...because there are 5 entries for ITB001, and 2 for MKB114

If we were to group by two columns:

select Subject, Semester, Count(*)
from Subject_Selection
group by Subject, Semester

we would get this:

+---------+----------+-------+
| Subject | Semester | Count |
+---------+----------+-------+
| ITB001  |        1 |     3 |
| ITB001  |        2 |     2 |
| MKB114  |        1 |     2 |
+---------+----------+-------+

This is because, when we group by two columns, it is saying "Group them so that all of those with the same Subject and Semester are in the same group, and then calculate all the aggregate functions (Count, Sum, Average, etc.) for each of those groups". In this example, this is demonstrated by the fact that, when we count them, there are three people doing ITB001 in semester 1, and two doing it in semester 2. Both of the people doing MKB114 are in semester 1, so there is no row for semester 2 (no data fits into the group "MKB114, Semester 2")

Hopefully that makes sense.

Select multiple columns from a table, but group by one

I use this trick to group by one column when I have a multiple columns selection:

SELECT MAX(id) AS id,
    Nume,
    MAX(intrare) AS intrare,
    MAX(iesire) AS iesire,
    MAX(intrare-iesire) AS stoc,
    MAX(data) AS data
FROM Produse
GROUP BY Nume
ORDER BY Nume

This works.

mysql sum two columns on the same values of another two columns

Since you want your results grouped by date you should use GROUP BY to group by date.

Then you can use SUM get the sum for each day.

But since you want different sums depending on the value of another column you have to use a SUM for every column you want and CASE inside in order to check the value of the other column and add the corresponding amount only if the column has the desired value.

Something like this should work.

You can also check the fiddle here
http://sqlfiddle.com/#!9/05ab84/4

SELECT 
`TradeDate`,
SUM(CASE
    WHEN `BaseCCY`='EUR' THEN `BaseAmt`
    WHEN `TermCCY`='EUR' THEN `TermAmt`
    ELSE 0
    END) AS `EUR`,
SUM(CASE
    WHEN `BaseCCY`='USD' THEN `BaseAmt`
    WHEN `TermCCY`='USD' THEN `TermAmt`
    ELSE 0
    END) AS `USD`,
SUM(CASE
    WHEN `BaseCCY`='AUD' THEN `BaseAmt`
    WHEN `TermCCY`='AUD' THEN `TermAmt`
    ELSE 0
    END) AS `AUD`,
SUM(CASE
    WHEN `BaseCCY`='CNH' THEN `BaseAmt`
    WHEN `TermCCY`='CNH' THEN `TermAmt`
    ELSE 0
    END) AS `CNH`
FROM `test` GROUP BY `TradeDate`

---

Related Topics