SQL questions on these

Question

I have 4 tables. One is called artist. Here is the table structure:

artistID   lastname   firstname   nationality   dateofbirth   datedcease

The other table is called work

workId   title   copy   medium   description   artist ID

Trans table

TransactionID   Date Acquired   Acquistionprice   datesold 
askingprice    salesprice    customerID     workID

Customer table

customerID    lastname    Firstname     street     city     state 
zippostalcode     country    areacode    phonenumber    email

First question is which artist has the most works of artsold and how many of the artist works have been sold.

My SQL query is below

SELECT * 
FROM dtoohey.artist A1 
INNER JOIN 
    (SELECT 
         COUNT(W1.ArtistID) AS COUNTER, artistID  
     FROM dtoohey.trans T1
     INNER JOIN dtoohey.work W1 ON W1.workid = T1.Workid
     GROUP BY W1.artistID) TEMP1 ON TEMP1.artistID = A1.artistID
WHERE 
    A1.artistID = TEMP1.artistId
ORDER BY 
    COUNTER desc;

I am to get the whole table but I want to show only the first row which is the highest count - how do I do that??

qns 2 is sales of which artist's work have resulted in the highest average profit(i.e) the average of the profits made on each sale of worksby an artist), and what is that amount.

My SQL query is below

SELECT 
    A1.artistid, A1.firstname 
FROM
    (SELECT 
         (salesPrice - AcquisitionPrice) as profit, 
         w1.artistid as ArtistID 
     FROM dtoohey.trans T1
     INNER JOIN dtoohey.WORK W1 ON W1.workid = T1.workid) TEMP1
INNER JOIN 
    dtoohey.artist A1 ON A1.artistID = TEMP1.artistID
GROUP BY 
    A1.artistid
HAVING 
    MAX(PROFIT) = AVG(PROFIT);

I'm not able to execute it

MySQL and Oracle use different syntaxes to solve these types of problem. Are you really looking for for one answer which works for both flavours of RDBMS? — APC, Oct 07 '12 at 11:22

score 0 · Answer 1 · edited Oct 06 '12 at 10:34

0

Use limit 1 for your fist question

edited Oct 06 '12 at 10:34

Sergey K.

24,894
13
106
174

answered Oct 06 '12 at 10:31

Abubakkar

15,488
8
55
83

i used limit 1 but it still cannot work coould you advice where to put in? – user1692068 Oct 06 '12 at 10:43
after your `ORDER BY COUNTER desc` clause – Abubakkar Oct 06 '12 at 10:52
Yes i did but it did not show the any rows. but if i put ROWNUM =1 only show artist ID =1 not the rownum i think – user1692068 Oct 06 '12 at 11:08

Barmar · Answer 2 · 2012-10-06T10:54:01.520

0

Your second problem can be solved exactly the same as the first one. Just replace

COUNT(W1.ArtistID)

with

AVG(salesPrice - AcquisitionPrice) as AvgProfit

and then use:

ORDER BY AvgProfit DESC
LIMIT 1

The full query should be:

SELECT A1.artistid, A1.firstname, TEMP1.avgProfit
FROM (SELECT AVG(salesPrice - AcquisitionPrice) as avgProfit, W1.artistid as artistid
      FROM dtoohey.trans T1
      INNER JOIN dtoohey.WORK W1
      ON W1.workid = T1.workid
      GROUP BY artistid
      ORDER BY avgProfit DESC
      LIMIT 1) TEMP1
INNER JOIN dtoohey.artist A1
ON A1.artisid = TEMP1.artistid

edited Oct 06 '12 at 10:54

answered Oct 06 '12 at 10:37

Barmar

741,623
53
500
612

hi i dun really understand could you type out the quey? – user1692068 Oct 06 '12 at 10:42
Added complete query to answer. – Barmar Oct 06 '12 at 10:54
I don't know why it's complaining. I just tried it in my MySQL system and it didn't complain about a syntax error. There's a right parenthesis on line 8, it matches the left parenthesis on line 2 before "SELECT". – Barmar Oct 06 '12 at 11:08
oh well is there another way to do it without using limit 1? as it cause errors – user1692068 Oct 06 '12 at 11:13
Hi i did not make any mistake when copying – user1692068 Oct 06 '12 at 11:18
Can you make a sqlfiddle with some sample data? – Barmar Oct 06 '12 at 11:21

SQL questions on these

2 Answers2