How to build such query in MySQL

Question

I need advice of MySQL genius.

Table1 'objects':

ID (int,key,autoincrement) | hash(text) | name(text)

Table2 'logs':

ID (int,key,autoincrement) | hash(text) | type(int) | time(text) | sum(int)

Table1 'objects' links with Table2 'logs' by column 'hash'. That column contains MD5 values.

I need to get data with type=1 or type=2 in such way for every object:

Name | sum all rows for this name and type=1 | sum all rows for this name and type=2

Tried queries like this

SELECT name, SUM(sum) as tsum 
FROM objects RIGHT JOIN logs using(hash) 
WHERE (type='1' OR type='2') 
GROUP BY type

But it forms data in format

Name | sum all rows for this name and type=1
Name | sum all rows for this name and type=2

M Khalid Junaid · Accepted Answer · 2015-04-28 11:51:11Z

2

You can use CASE to get sum based on type 1/2 and use name column in group by

SELECT name,
SUM(case when type='1' then sum else 0 end) as firstsum ,
SUM(case when type='2' then sum else 0 end) as  secondsum
FROM objects 
RIGHT JOIN logs using(hash) 
WHERE (type='1' OR type='2') 
GROUP BY name

answered Apr 28, 2015 at 11:51

M Khalid Junaid

64.6k10 gold badges93 silver badges120 bronze badges

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

Collectives™ on Stack Overflow

How to build such query in MySQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related