CODEWITHSUNDEEP
sqloracle-database
aofe1337
aofe1337

Reputation: 67

Merge output as one

    Select Category, Books.ISBN,
Orderitems.Quantity * (Books.Retail - Books.Cost) AS Category_Profit
From BOoks
INNER JOIN Orderitems
ON BOOKS.ISBN=ORDERITEMS.ISBN

Example output:

Category            ISBN           Category_Profit
Family life         1234                 50
Family Life         1234                 50
Family Life         1234                 100
Fitness             4321                 10
Fitness             4321                 20

So forth and so forth,

How can I make the output calculate all the values for each category into one line?

I.e

Family Life        1234     200
Fitness            4321     30

Upvotes: 1

Views: 73

Answers (3)

SunilA
SunilA

Reputation: 583

Group by can be used to solve your problem.

Note: In Group by clause , a set of table rows can be grouped based on certain columns and in the select clause either the group by columns or aggregate function(SUM,MIN,MAX,Count etc) on other columns can be shown.

Reference : http://www.dofactory.com/sql/group-by

Use group by as is done below. Hope this solves the issue.

 Select Category, Books.ISBN,
 SUM(Orderitems.Quantity * (Books.Retail - Books.Cost)) AS Category_Profit
 From BOoks
 INNER JOIN Orderitems
 ON BOOKS.ISBN=ORDERITEMS.ISBN
 Group by Category,ISBN

Upvotes: 1

EoinS
EoinS

Reputation: 5482

Because you already have this as a starting point, use exactly what you have as a temp table, and pull data from that:

Select Category, ISBN, Sum(Category_Profit) From
(
select Category, Books.ISBN as ISBN,
Orderitems.Quantity * (Books.Retail - Books.Cost) AS Category_Profit
From Books
INNER JOIN Orderitems
ON BOOKS.ISBN=ORDERITEMS.ISBN
) temp
group by Category, ISBN

You organized the data really well, so implementing a sum on the Profit is easy. You group by Category and ISBN to get all unique pairs of those columns with the corresponding Profit.

If you do not want to use a sub-query you can sum in your query (but I feel it's something helpful to use my existing query as a sub-query before altering it, just to make sure it works:

select Category, Books.ISBN,
SUM(Orderitems.Quantity * (Books.Retail - Books.Cost)) AS Category_Profit
From Books
INNER JOIN Orderitems
ON BOOKS.ISBN=ORDERITEMS.ISBN
 temp
group by Category, Books.ISBN

Upvotes: 1

Ani Menon
Ani Menon

Reputation: 28219

Use GROUP_BY & SUM, Syntax :

SELECT column_name, SUM(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;

Refer: SQL GROUP_BY

On you table you may run :

Select Category, ISBN, Sum(Category_Profit) From Table1
group by Category, ISBN;

Table1:

Category            ISBN           Category_Profit
Family life         1234                 50
Family Life         1234                 50
Family Life         1234                 100
Fitness             4321                 10
Fitness             4321                 20

Output:

|    Category | ISBN | Sum(Category_Profit) |
|-------------|------|----------------------|
| Family life | 1234 |                  200 |
|     Fitness | 4321 |                   30 |

Fiddle

Upvotes: 0

Related Questions