Merge output as one

Question

    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

EoinS · Answer 1 · 2016-05-28T06:02:35.840

1

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

edited May 28 '16 at 06:02

answered May 28 '16 at 05:53

EoinS

5,405
1
19
32

so what I have is now different ISBN's in each category I.e Cat , ISBN, Price, (Comp 1234 50) (Comp 3333 50) To merge these two again would it be another use of the group by or sum? I'm thinking sum where maybe I can find a way to calculate all the ISBN's in the category – aofe1337 May 28 '16 at 06:03
so my first set of code shows you how to pull the data from your table. Take your exact query and but put brackets around it(your query) – EoinS May 28 '16 at 06:14
then select Cat, ISBN and sum(Category_profit) from (your query) group by cat, isbn – EoinS May 28 '16 at 06:14

score 1 · Answer 2 · answered May 28 '16 at 06:40

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

Ani Menon · Answer 3 · 2016-05-28T06:01:12.697

0

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

edited May 28 '16 at 06:01

answered May 28 '16 at 05:52

Ani Menon

27,209
16
105
126

what if Category Family Life had two different ISBN numbers attached to it? So ISBN 1234 and 1235 are both in category Family life. and you want to display them both in the same row seperated by commas? Would that be possible? – aofe1337 May 28 '16 at 08:32
Yes you may do that. Read this : [GROUP BY to combine/concat a column](http://stackoverflow.com/questions/15154644/group-by-to-combine-concat-a-column) – Ani Menon May 28 '16 at 09:05

Merge output as one

3 Answers3