MySQL Forums
Forum List  »  Spanish

Re: consulta mysql
Posted by: laptop alias
Date: September 20, 2012 11:05AM

DROP TABLE IF EXISTS my_table;
CREATE TABLE my_table
(id CHAR(1) NOT NULL 
,type TINYINT NOT NULL
,value DECIMAL (5,2)
,PRIMARY KEY (id,type)
);

INSERT INTO my_table VALUES
('a',1,0.75),
('a',2,0.4),
('b',1,0.25);

DROP TABLE IF EXISTS my_other_table;
CREATE TABLE my_other_table
(type TINYINT NOT NULL PRIMARY KEY
,amount INT NOT NULL 
);

INSERT INTO my_other_table VALUES
('1',1000),
('2',500);

SELECT x.id
     , MAX(CASE WHEN x.type = 1 THEN x.value * y.amount END) type1
     , MAX(CASE WHEN x.type=2 THEN x.value * y.amount END) type2 
     , SUM(x.value * y.amount) total
  FROM my_table x 
  JOIN my_other_table y 
    ON y.type = x.type
 GROUP 
    BY id;

Options: ReplyQuote


Subject
Views
Written By
Posted
1805
September 20, 2012 09:58AM
Re: consulta mysql
845
September 20, 2012 11:05AM
787
September 20, 2012 11:48AM
920
September 21, 2012 02:18AM
641
September 21, 2012 09:03AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.