MySQL - select multiple rows from one table into one result row

Question

I have a table where are periodically imported values. For statistics use I need to create diff view of two dates.

Table looks like

name	cpu_count	memory_count	import_date
alpha	2	4	2022-09-28
alpha	2	6	2022-10-01

and I need select columns like name, cpu_old, memory_old, cpu_new, memory_new into one row.

name	cpu_old	memory_old	cpu_new	memory_new
alpha	2	4	2	6

Can anybody help me? I was trying it with UNION, GROUP etc but every time I've get two rows.

Unfortunately no. Import run every week. There will be where import_date = xx — Roman Müller
– Roman Müller, Commented Oct 1, 2022 at 9:31
Please update your question, as now it is unclear. Do you have 1 or 2 tables? What is the desired output? What queries have you tried? — Michel
– Michel, Commented Oct 3, 2022 at 4:00
Its solved, first solution from @sukalogika is expected behavior — Roman Müller
– Roman Müller, Commented Oct 3, 2022 at 7:08

sukalogika · Accepted Answer · 2022-10-03 03:48:14Z

2

SOLUTION 1

You have data something like this:

SQL for create table:

CREATE TABLE `my_cpu` (
  `name` varchar(32) NOT NULL,
  `cpu_count` tinyint(4) DEFAULT NULL,
  `memory_count` tinyint(4) DEFAULT NULL,
  `import_date` date NOT NULL,
  PRIMARY KEY (`name`,`import_date`)
) ENGINE=MyISAM DEFAULT CHARSET=ascii

RESULT:

SQL for create that result:

SELECT a.name,
    a.cpu_count AS cpu_old,
    a.memory_count AS memory_old,
    b.cpu_count AS cpu_new  ,
    b.memory_count AS memory_new
FROM `my_cpu`   a INNER JOIN `my_cpu` b ON a.name = b.name
WHERE a.import_date = "2022-09-28" AND b.import_date = "2022-10-01"

EXPLAINATION:

Because you store data every week, so you have 2 dates.
The data is saved in one table_names, lets say it "my_cpu".
Create self join from my_cpu. So you have 2 table (a and b). With condition a.name = b.name
Filter where a.import_date = your_old_date and b.import_date = your_new_date

FURTHER READ:

https://www.w3schools.com/sql/sql_join_self.asp

ALTERNATE SOLUTION:

This would be suitable for random/ different import date

Using group_concat and substring_index for different import date.

RESULT:

SQL:

SELECT  `name`,

    SUBSTRING_INDEX(SUBSTRING_INDEX(cpu_count,",",2),",",-1) cpu_old,
    SUBSTRING_INDEX(SUBSTRING_INDEX(memory_count,",",2),",",-1) memory_old,
    
    SUBSTRING_INDEX(cpu_count,",",1) cpu_new,
    SUBSTRING_INDEX(memory_count,",",1) memory_new
FROM
(

SELECT  `name`,
    GROUP_CONCAT(`cpu_count` ORDER BY `import_date` DESC) cpu_count,
    GROUP_CONCAT(`memory_count` ORDER BY `import_date` DESC) memory_count
FROM    `my_cpu`
GROUP
BY NAME
)

AS dbx

EXPLAINATION:

Create query for get cpu and memory in ordered by import_date in descending each cpu_name

THE QUERY:

SELECT  `name`,
    GROUP_CONCAT(`cpu_count` ORDER BY `import_date` DESC) cpu_count,
    GROUP_CONCAT(`memory_count` ORDER BY `import_date` DESC) memory_count
FROM    `my_cpu`
GROUP
BY NAME

WOULD REPRODUCE:

Search with substring_index,

The lastet would be the first letter before (,)

The second(oldest) would be the the second letter after the (,)

edited Oct 3, 2022 at 3:48

answered Oct 1, 2022 at 9:47

sukalogika

5994 gold badges11 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

flyingfox Over a year ago

How will you do if my_cpu have 100 rows with different import_date?

Abdulrahman Ali Over a year ago

@lucumt OP's question is about the diff between two import_date, no more. Unless I misunderstood something.

sukalogika Over a year ago

@lucumt I add alternate query with different date. Maybet its would be a solution

Collectives™ on Stack Overflow

MySQL - select multiple rows from one table into one result row

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related