MySQL query to group by two columns?
You need to give it a column to count, and group the rest:
select count(country) as COUNT, COUNTRY, DATE_OF_SALE
from DB.TABLE ORDER BY DATE_OF_SALE DESC
group by COUNTRY, DATE_OF_SALE
That should do the trick. Except that you're counting one of the thing's you're returning. You should find another column and count that. Usually a unique ID is the best.
MySQL GROUP BY two columns
First, let's make some test data:
create table client (client_id integer not null primary key auto_increment,
name varchar(64));
create table portfolio (portfolio_id integer not null primary key auto_increment,
client_id integer references client.id,
cash decimal(10,2),
stocks decimal(10,2));
insert into client (name) values ('John Doe'), ('Jane Doe');
insert into portfolio (client_id, cash, stocks) values (1, 11.11, 22.22),
(1, 10.11, 23.22),
(2, 30.30, 40.40),
(2, 40.40, 50.50);
If you didn't need the portfolio ID, it would be easy:
select client_id, name, max(cash + stocks)
from client join portfolio using (client_id)
group by client_id
+-----------+----------+--------------------+
| client_id | name | max(cash + stocks) |
+-----------+----------+--------------------+
| 1 | John Doe | 33.33 |
| 2 | Jane Doe | 90.90 |
+-----------+----------+--------------------+
Since you need the portfolio ID, things get more complicated. Let's do it in steps. First, we'll write a subquery that returns the maximal portfolio value for each client:
select client_id, max(cash + stocks) as maxtotal
from portfolio
group by client_id
+-----------+----------+
| client_id | maxtotal |
+-----------+----------+
| 1 | 33.33 |
| 2 | 90.90 |
+-----------+----------+
Then we'll query the portfolio table, but use a join to the previous subquery in order to keep only those portfolios the total value of which is the maximal for the client:
select portfolio_id, cash + stocks from portfolio
join (select client_id, max(cash + stocks) as maxtotal
from portfolio
group by client_id) as maxima
using (client_id)
where cash + stocks = maxtotal
+--------------+---------------+
| portfolio_id | cash + stocks |
+--------------+---------------+
| 5 | 33.33 |
| 6 | 33.33 |
| 8 | 90.90 |
+--------------+---------------+
Finally, we can join to the client table (as you did) in order to include the name of each client:
select client_id, name, portfolio_id, cash + stocks
from client
join portfolio using (client_id)
join (select client_id, max(cash + stocks) as maxtotal
from portfolio
group by client_id) as maxima
using (client_id)
where cash + stocks = maxtotal
+-----------+----------+--------------+---------------+
| client_id | name | portfolio_id | cash + stocks |
+-----------+----------+--------------+---------------+
| 1 | John Doe | 5 | 33.33 |
| 1 | John Doe | 6 | 33.33 |
| 2 | Jane Doe | 8 | 90.90 |
+-----------+----------+--------------+---------------+
Note that this returns two rows for John Doe because he has two portfolios with the exact same total value. To avoid this and pick an arbitrary top portfolio, tag on a GROUP BY clause:
select client_id, name, portfolio_id, cash + stocks
from client
join portfolio using (client_id)
join (select client_id, max(cash + stocks) as maxtotal
from portfolio
group by client_id) as maxima
using (client_id)
where cash + stocks = maxtotal
group by client_id, cash + stocks
+-----------+----------+--------------+---------------+
| client_id | name | portfolio_id | cash + stocks |
+-----------+----------+--------------+---------------+
| 1 | John Doe | 5 | 33.33 |
| 2 | Jane Doe | 8 | 90.90 |
+-----------+----------+--------------+---------------+
MySQL group by with 2 columns when values are interchanged in columns
One way to determine the "thread" for each row is CONCAT() the LEAST of the two numbers with the GREATEST of the same two numbers.
We can then GROUP BY on the "thread", to get the latest generated_time. In HAVING clause, we filter out only those "thread", which has atleast one 'INCOMING' message with 'REVIEW' type.
View on DB Fiddle
SELECT m1.*
FROM message AS m1
JOIN (SELECT Concat(Least(m.from_number, m.to_number), '|',
Greatest(m.from_number,
m.to_number))
AS
thread,
Max(m.generated_time)
AS max_generated_time
FROM message AS m
GROUP BY thread
HAVING Sum(m.direction = 'INCOMING'
AND m.type = 'REVIEW')) AS dt
ON dt.thread = Concat(Least(m1.from_number, m1.to_number), '|',
Greatest(m1.from_number, m1.to_number))
AND dt.max_generated_time = m1.generated_time;
Result
| id | to_number | from_number | message | direction | type | generated_time |
| --- | ------------ | ------------ | --------------- | --------- | ------ | ------------------- |
| 3 | +15005550004 | +16232950692 | How are you ? | OUTGOING | | 2019-07-13 21:15:00 |
| 5 | +16232950692 | +15005550001 | Have a nice day | INCOMING | REVIEW | 2019-07-12 12:17:00 |
Sidenote:
- Above approach (and your current schema design) is not able to use indexes, and hence it will not be performant.
- I would rather redesign the schema by creating two additional Master tables. One Master table would be storing the phone numbers:
phone_id, andnumber - Another Master table would be storing the "Thread", which will contain the
phone_idvalues andthread_id. You can then use thisthread_idin yourmessagetable, instead of storing the phone numbers.
MySQL - Group by multiple columns from same table
After lot of tries, I am able to get the data I want
Query ['Female']
SELECT sno, bd.booking_id, bd.room_type, bd.gender, bd.age
FROM customer_data bd
INNER JOIN (
SELECT booking_id, GROUP_CONCAT(DISTINCT gender) AS g
FROM customer_data
WHERE gender!='' AND age>0
GROUP BY booking_id
HAVING COUNT(booking_id) > 1
ORDER BY booking_id ASC, gender DESC
) cbd
WHERE cbd.booking_id = bd.booking_id AND cbd.g = 'Female'
Query ['Male']
SELECT sno, bd.booking_id, bd.room_type, bd.gender, bd.age
FROM customer_data bd
INNER JOIN (
SELECT booking_id, GROUP_CONCAT(DISTINCT gender) AS g
FROM customer_data
WHERE gender!='' AND age>0
GROUP BY booking_id
HAVING COUNT(booking_id) > 1
ORDER BY booking_id ASC, gender DESC
) cbd
WHERE cbd.booking_id = bd.booking_id AND cbd.g ='Male'
Query ['Male and Female']
SELECT sno, bd.booking_id, bd.room_type, bd.gender, bd.age
FROM customer_data bd
INNER JOIN (
SELECT booking_id, GROUP_CONCAT(DISTINCT gender ORDER BY gender DESC) AS g
FROM customer_data
WHERE gender!='' AND age>0
GROUP BY booking_id
HAVING COUNT(booking_id) > 1
ORDER BY booking_id ASC, gender DESC
) cbd
WHERE cbd.booking_id = bd.booking_id AND cbd.g = 'Male,Female'
Grouping by multiple columns in MYSQL across multiple tables (INNER JOIN)
I found the answer myself:
SELECT o.orderid, s.suppliername, COUNT(p.productname) AS numberOfProducts
FROM Orders o
JOIN OrderDetails od
ON o.orderid = od.orderid
JOIN Products p
ON p.productid = od.productid
JOIN Suppliers s
ON s.supplierid = p.supplierid
GROUP BY o.orderid, s.suppliername
HAVING o.orderid = 10300;
The mainissue was that
ON o.shipperid = s.supplierid had to be
ON s.supplierid = p.supplierid
friendly users on SO helped me out on that :)
MySQL: GROUP by with multiple columns
You are on the right track... Do a pre-query as a basis of what records you want, then re-join to the same original table on the qualifying date condition..
select
PreQuery.Portal,
PreQuery.LatestPostNumber,
T2.User,
T2.Text,
T2.Date
from
( select
t1.Portal,
max( t1.PostNumber ) as LatestPostNumber
from
GuestBook T1
group by
t1.Portal ) PreQuery
JOIN GuestBook T2
on PreQuery.Portal = T2.Portal
AND PreQuery.PostNumber = T2.PostNumber
Related Topics
Decode( ) Function in SQL Server
Postgresql Extract Last Row for Each Id
Difference Between Select Unique and Select Distinct
What's Best SQL Datatype for Storing JSON String
Select Not in Multiple Columns
How to Remove the First Characters of a Specific Column in a Table
What Should I Name a Table That Maps Two Tables Together
Sqlite String Contains Other String Query
Understanding How Join Works When 3 or More Tables Are Involved. [Sql]
How to Create Simple Fuzzy Search with Postgresql Only
Relationship of Primary Key and Clustered Index
SQL Server Plans:Difference Between Index Scan/Index Seek
Copy a Table (Including Indexes) in Postgres
Job Queue as SQL Table with Multiple Consumers (Postgresql)