CODEWITHSUNDEEP
mysql
user3722205
user3722205

Reputation: 3

make the price of one line the price of all lines

I just started using mysql for SAP.

I would like to get this result:

DocEntry Description Price
00001    item A      100.00
00003    item C      110.00

From this data

DocEntry  Description Quantity Price
00001     item A       2       300.00
00001     freight      -       100.00
00002     item A       1       300.00
00003     item C       1       300.00
00003     freight      -       110.00

I tried to filter the items with freight through this:

create view table_a 
as 
select DocEntry, Description, Price 
from inv1 where Description like 'freight'

create view table_b 
as 
select DocEntry, Description, Price
from inv1
where exist (select * from table_a where table_a.DocEntry = table_b.DocEntry

I don't know though what to do with price?

Thanks!

Upvotes: 0

Views: 55

Answers (2)

Bohemian
Bohemian

Reputation: 425208

It looks like you want the freight fit each item:

select distinct a.DocEntry, a.Description, b.Price
from inv1 a
join inv1 b on a.DocEntry = b.DocEntry
where a.Description != 'freight'
and b.Description = 'freight'

although the data does not require it, the distinct keyword has been added in case there are multiple "item" rows for each "freight" row. If the is no possibility of that ever happening, distinct may be removed.

Upvotes: 1

Strawberry
Strawberry

Reputation: 33945

It's not quite clear what you're asking, but maybe something like this...

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(DocEntry INT NOT NULL,  Description VARCHAR(12) NOT NULL, Quantity INT NULL,Price INT NOT NULL,PRIMARY KEY(DocEntry,Description));

INSERT INTO my_table VALUES
(1,'item A',2,300),
(1,'freight',NULL,100),
(2,'item A',1,300),
(3,'item C',1,300),
(3,'freight',NULL,110);

mysql> SELECT * FROM my_table;
+----------+-------------+----------+-------+
| DocEntry | Description | Quantity | Price |
+----------+-------------+----------+-------+
|        1 | freight     |     NULL |   100 |
|        1 | item A      |        2 |   300 |
|        2 | item A      |        1 |   300 |
|        3 | freight     |     NULL |   110 |
|        3 | item C      |        1 |   300 |
+----------+-------------+----------+-------+

SELECT docentry
     , MAX(CASE WHEN description = 'freight' THEN price END) price
     , MAX(CASE WHEN quantity IS NOT NULL THEN description END) description 
  FROM my_table 
 GROUP 
    BY docentry 
HAVING price IS NOT NULL;
+----------+-------+-------------+
| docentry | price | description |
+----------+-------+-------------+
|        1 |   100 | item A      |
|        3 |   110 | item C      |
+----------+-------+-------------+

Upvotes: 0

Related Questions