Myujth

CREATE TABLE most_prof
(
 pub_id CHAR(4) NOT NULL,
 top_profit VARCHAR(80) NOT NULL,
 date_time DATETIME,
 PRIMARY KEY (top_profit)
)

INSERT INTO most_prof (pub_id, top_profit, date_time)
 SELECT t.pub_id, t.title, t.pubdate
 FROM titles AS t 

This is homework so I am by no means asking for an answer, just some guidance!
In the select statement, where I have t.title, I need to return the title that is associated with a calculated top profit. So I need to take a few columns, calculate the top profit, and return that title associated with it. Each pub_id has multiple titles associated with it but I just need the one with the top profit and the pubdate associated with it.

I've tried a few things but I keep getting this error

Column 'titles.pub_id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Since this is homework, please no answers, just looking for guidance as best you can.

EDIT:

In the SELECT statement t.title returns the title of a book, however this has to be a specific title. To find that, I need to use other columns in the table to calculate each publishers titles profit - something like (((price - (royalty * 1.0 / 100)) * ytd_sales) - advance). Then I need to use that to return 1 title that has the highest profit from each publisher.

2ND EDIT: This is what gives me the error as stated above

INSERT INTO most_prof(pub_id, top_profit, date_time)
 SELECT t.pub_id, (((price - (royalty * 1.0 / 100)) * ytd_sales) - 
advance), t.pubdate
 FROM titles AS t 
 GROUP BY t.title;

3rd EDIT:

INSERT INTO most_prof (pub_id, top_profit, date_time)
 SELECT DISTINCT
 pub_id, MAX (((price - (royalty * 1.0 / 100)) * ytd_sales) - advance), 
 pubdate
 FROM titles
 GROUP BY pub_id

Now this would return the pub_id, the top_profit, and the pubdate however:

1. While it returns the correct profit I need the title associated with that, not the profit itself


2. selecting pubdate causes that aggregate error again - cant seem to shake that

EDIT 4: Based on comment

I've added a screen grab of what I got so far - and it's looking good. As you can see i've got multiple titles for each pub_id - how can I return only the most profitable title for each of the 3 pub_id's??

First off, I would like to thank you for this :

This is homework so I am by no means asking for an answer, just some
guidance!

and this :

Since this is homework, please no answers, just looking for guidance
as best you can.

What I'll try to do, is giving you a some hints that would lead you to your answer.

(From the given information) I assume this homework, will be using aggregation functions (e.g. SUM(), AVG(), MIN(),MAX() ..etc), GROUP BY, (maybe HAVING), and ORDER BY.

Remember, whenever you use aggregation functions, you'll need to specify the columns that are under SELECT, and they're not contained with an aggregation function in the GROUP BY clause.

So, if you did this :

SELECT t.pub_id, SUM(((price - (royalty * 1.0 / 100)) * ytd_sales) - advance), t.pubdate
FROM titles AS t 
GROUP BY t.title;

it'll give you an error, because there is no t.title under the SELECT clause. So, the correct one would be something like this :
GROUP BY t.pub_id, t.pubdate

If you want it to be grouped by t.title, you'll need to do this :

SELECT t.title, SUM(((price - (royalty * 1.0 / 100)) * ytd_sales) - advance)
 FROM titles AS t 
 GROUP BY t.title;

if there is a required period of time (say you want to get the profits of the last 3 months for each title), then you could use t.pubdate but this will be under the WHERE (or HAVING) clause, and the t.title will be under the SELECT clause.

Now, after you've done the calculations, you'll need to reorder the results by the highest profit, and then you just select the top profit. Some people likes it easier to contain two aggregation functions (say MIN(SUM(...)) ) to get the highest or lowest, which will avoid them from using ORDER BY.

UPDATE (Based on comments)

Since you've covered sub-queries, you're very close to the answer.

After running the second query for titles, I'm able to see all the
titles and their profits, I can't seem to figure out how to correlate
those titles with their corresponding pub_id. Lets say I have 3
pub_id's - and each pub_id has multiple titles. I can't figure out how
to correlate the top_profit title for a particular pub_id, if that
makes sense.

The correct way to get the profits is to find the unique id that you can use to sum up the profits for. for your case you said pub_id has multiple title then, I assume that pub_id is the primary key and the titles are assigned to each primary key. (for instance, a publisher can published multiple books) so you need to get the publisher profits from all the published books.

With this, you know that you'll need to get the publisher and not the books. So, in your query, you'll need to replace titles with pub_id and keep the rest as is. this would sum-up all profit based on pub_id, which is the main requirement that you need.

If you need to get the profit for each publisher's book, you can use

SELECT t.pub_id, t.title SUM(....) as Profit
FROM titles AS t 
GROUP BY t.pub_id, t.title; 

This is like telling SQL : Hey, get me the profit of each book for publisher. which will give you the profit for each book.

But if you do this :

SELECT t.pub_id, SUM(....) as Profit
FROM titles AS t 
GROUP BY t.pub_id; 

It'll get you the profit for each publisher (meaning if a publisher has 5 books, it'll be summed up).

So, the more columns you include in the GROUP BY the SUM() will be detailed further.

If you need to enclose it with a sub-query, there are different ways to do this but I'll give you two ways.

Method one :

SELECT * 
FROM (
 SELECT t.pub_id, SUM(....) as Profit
 FROM titles AS t 
 GROUP BY t.pub_id; 
) D -- alias is mandatory

Method two :

SELECT *
FROM titles t1 
JOIN (
 SELECT t.pub_id, SUM(....) as Profit
 FROM titles AS t 
 GROUP BY t.pub_id; 
) t2 ON t1.pub_id = t2.pub_id

So, you can use method one or method two, whatever you like.

UPDATE (Based on comments) #2

I've added a screen grab of what I got so far - and it's looking good.
As you can see I've got multiple titles for each pub_id - how can I
return only the most profitable title for each of the 3 pub_id's??

Great, you're almost done. You'll need to use a function named ROW_NUMBER() to number the rows based on our custom condition. So we will add this :
ROW_NUMBER() OVER(PARTITION BY t1.pub_id ORDER BY Profit DESC) AS ProfitOrder
The new order will be partitioned by t1.pub_id and order each partition them by the highest to the lowest Profit (for each id group).

Our query should something like this :

SELECT 
 t1.title
 , t1.pub_id
 , t1.pubdate
 , Profit
 , ROW_NUMBER() OVER(PARTITION BY t1.pub_id ORDER BY Profit DESC) AS ProfitOrder
 FROM titles t1 
 JOIN (
 SELECT t.pub_id, t.title, SUM(price * ytd_sales) as Profit
 FROM titles AS t 
 GROUP BY t.pub_id 
 ) t2 ON t1.pub_id = t2.pub_id

If you run the above query, the ProfitOrder number 1 for each pub_id will have the highest Profit, this verifies that our records is sorted out correctly, and we just need to get the first 3 rows from each group using the ProfitOrder.
and if we need to do this :

SELECT 
 t1.title
, t1.pub_id
, t1.pubdate
, Profit
, ROW_NUMBER() OVER(PARTITION BY t1.pub_id ORDER BY Profit DESC) AS ProfitOrder
FROM titles t1 
JOIN (
 SELECT t.pub_id, t.title, SUM(price * ytd_sales) as Profit
 FROM titles AS t 
 GROUP BY t.pub_id 
) t2 ON t1.pub_id = t2.pub_id
WHERE 
 ProfitOrder <= 3

it will yield an error, because we can't use column alias under WHERE, unless if we convert the query into a sub-query. So, we need to contain this query (again) into a sub-query. Like this one :

SELECT *
FROM (
 SELECT 
 t1.title
 , t1.pub_id
 , t1.pubdate
 , Profit
 , ROW_NUMBER() OVER(PARTITION BY t1.pub_id ORDER BY Profit DESC) AS ProfitOrder
 FROM titles t1 
 JOIN (
 SELECT t.pub_id, t.title, SUM(price * ytd_sales) as Profit
 FROM titles AS t 
 GROUP BY t.pub_id 
 ) t2 ON t1.pub_id = t2.pub_id
) D 
WHERE 
 ProfitOrder <= 3

Now, verify the records again, if the output is what you expected. Then, you will only need to use the top SELECT * and select the columns that you need to be showed only. LIKE SELECT pub_id, Profit, pubdate, and include it under your INSERT but make sure that you matched the columns between the INSERT and your SELECT.