Gautam Nath Gautam Nath - 6 months ago 7
SQL Question

Merging values for one column and summing up the values in the second column

I'm a total newbie to Oracle SQL. I'm trying to perform an operation on about three tables. Basically, I have three tables: Artist, Transaction, Work. I'm trying to retrieve the artist who has the most works of art sold. When I run the query I get duplicate values and I'm not entirely sure how to merge the columns.

Here is the query I initially execute

SELECT FIRSTNAME || '' || LASTNAME AS ARTISTNAME, TITLE, SALESPRICE
FROM DTOOHEY.ARTIST A, DTOOHEY.WORK W, DTOOHEY.TRANS T
WHERE A.ARTISTID = W.ARTISTID
AND W.WORKID = T.WORKID
AND DATESOLD IS NOT NULL;


Here is the result.
What I want to achieve is to merge the Artist Names and then have their Sales Price summed up. For example, for the Artist Joan Miro I'd like for it to show only one value as "Joan Miro" and the Sales Price is a sum of the merged rows (400+200 = 600). So something like

Artist Name | SalesPrice

Joan Miro | 600


Is such a thing possible? I am not entirely sure how to use the MERGE function and I'm not sure if that's what I need to use here.

EDIT:

Sorry, I forgot to mention that I have indeed used GROUP BY. I have tried this query as well:

SELECT FIRSTNAME || '' || LASTNAME AS ARTISTNAME, TITLE, SUM(SALESPRICE)
FROM DTOOHEY.ARTIST A, DTOOHEY.WORK W, DTOOHEY.TRANS T
WHERE A.ARTISTID = W.ARTISTID
AND W.WORKID = T.WORKID
AND DATESOLD IS NOT NULL
GROUP BY FIRSTNAME, LASTNAME;


I get an error saying:

ORA-00979: not a GROUP BY expression
00979. 00000 - "not a GROUP BY expression"
*Cause:
*Action:
Error at Line: 42 Column: 51

Answer

This is exactly what the group by clause is for. It lets you specify which columns should define a group of rows (in your case - the artist's first and last name) and makes you apply some aggregate function (in your case - sum) on the others. I.e.:

SELECT   FIRSTNAME || '' || LASTNAME AS ARTISTNAME, SUM(SALESPRICE)
FROM     DTOOHEY.ARTIST A, DTOOHEY.WORK W, DTOOHEY.TRANS T
WHERE    A.ARTISTID = W.ARTISTID AND
         W.WORKID = T.WORKID AND
         DATESOLD IS NOT NULL
GROUP BY FIRSTNAME, LASTNAME;

General comment:
Implicit joins (having multiple tables in the from clause) has been considered as deprecated, and ill-advised for some years now. You should probably use an explicit join clause:

SELECT   FIRSTNAME || '' || LASTNAME AS ARTISTNAME, SUM(SALESPRICE)
FROM     DTOOHEY.ARTIST A
JOIN     DTOOHEY.WORK W ON A.ARTISTID = W.ARTISTID
JOIN     DTOOHEY.TRANS T ON W.WORKID = T.WORKID
WHERE    DATESOLD IS NOT NULL
GROUP BY FIRSTNAME, LASTNAME;