CODEWITHSUNDEEP
mysql
966p
966p

Reputation: 575

How to build such query in MySQL

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

Upvotes: 0

Views: 21

Answers (1)

M Khalid Junaid
M Khalid Junaid

Reputation: 64476

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

Upvotes: 2

Related Questions