How do I solve this (SQL) query? [closed]

This is probably one of the simplest queries ever, and I’m probably just making it harder than it has to be, but I need it done.

So, there’s three tables:

TABLE AGENT
(
A_ID INTEGER PRIMARY KEY,
A_NAME VARCHAR(20) NOT NULL,
A_ADDR VARCHAR(30) NOT NULL,
A_PHN CHAR(14) NOT NULL,
A_EMAIL VARCHAR(20) NOT NULL);
)

TABLE E_TRANSACTION
(
T_ID INTEGER PRIMARY KEY,
T_AMT FLOAT NOT NULL,
T_DATE DATE NOT NULL,
A_ID INTEGER REFERENCES AGENT(A_ID);
)

TABLE ESTATE
(
E_ID INTEGER PRIMARY KEY,
E_ADDR VARCHAR(30) NOT NULL,
T_ID INTEGER REFERENCES E_TRANSACTION(T_ID));
)

The query I’m trying to run has to answer this question:
The Agent who sold the most number of properties between Jan-1-2015 to June-30-2015

So far, I have this:

select A_ID, count(A_ID)
from E_TRANSACTION
where t_date >= '01-Jan-2015' and t_date < '30-June-2015'
group by A_ID;

but it’s too vague and my instructor has told me to only have the output of the agent who has sold the most properties, in this case, the highest occurrence of A_ID in the E_TRANSACTION table. In other words, it should only output that one A_ID which has the mode number in the A_ID stack within the E_TRANSACTION table which basically displays the transactions made by that agent.

I’ve tried ORDER BY and GROUP BY by themselves, and I’ve even tried them together, but I haven’t gotten any results.

I tried joining the two tables E_TRANSACTION and AGENT to get the name to come up as well as the A_ID which the name is fixed with, but that didn’t work out either. I’m assuming it’s because I typed the code wrong and or I was committing illegal actions.

To this moment, I still can’t wrap my head around whether to use GROUP BY or ORDER BY, and whatever else I need to get this query to work.

Additional Info:

Using Oracle 11g Express Edition (sqlplus)
Running all of this on SQL command line
Has just about zero experience with SQL

Some SQL Info:

insert into AGENT
values(1111, ‘Charles Markley’, ‘111-PSHMT Way’, ‘111-112-1122’, ‘cm@sample.example’);

insert into AGENT
values(1112, ‘Laurene Lowrey’, ‘1010 Learners St’, ‘101-121-1211’, ‘ll@sample.example’);

insert into E_TRANSACTION
values(1001, 30000.50, ’12-Jan-2015′, 1111);

insert into e_transaction
values(1002, 80000.50, ’20-June-2015′, 1112);

insert into ESTATE
values(301, ‘666 Merry Ln’, 1001);

insert into ESTATE
values(302, ‘521 Ball Staint Ct’, 1002);

So, as some have mentioned, I haven’t given enough details for this query.

The attempt initially was to collect the number of transactions made by each Agent, which is discovered by finding how many times each agent’s A_ID occurred in the E_TRANSACTION table.

Why I had assumed that would work? I wasn’t accounting for there to be multiple ESTATEs associated with a TRANSACTION. I will have to change that in my requirements list. The way I have my code set up now is that there is NO more than one ESTATE per TRANSACTION. So, that means there will only be ONE Agent making that TRANSACTION which get’s someone ASSIGNED an ESTATE. So, each ESTATE has one solely unique TRANSACTION ID as well as the AGENT who made the transaction. An agent is allowed to sell up to 5 estates, but those estates only get assigned one transaction ID.

I presented a few lines of dummy-data that has already been implemented in my database. What I can’t get is the correct syntax or method needed to complete the query asked of me, which is given above in bold.

If you don’t feel like giving me specifics, then please at least work through what I’m doing wrong. Maybe explain why my methods don’t work and as to why using GROUP BY or ORDER BY don’t work in this example.

Again, any help would be grand. — That Random Guy

Answer

The important things here seem to be:

INNER JOIN
COUNT
GROUP BY
ROWNUM

So the following should do you:

SELECT * FROM (SELECT a.A_ID, a.A_NAME, COUNT(t.A_ID) AS TRANSACTION_COUNT
                FROM AGENT a
                INNER JOIN E_TRANSACTION t
                  ON t.A_ID = a.A_ID
                GROUP BY a.A_ID, a.A_NAME
                ORDER BY COUNT(t.A_ID) DESC)
  WHERE ROWNUM = 1;

SQLFiddle here

Edit

Note the use of the alias a following the AGENT table, and t following the E_TRANSACTION table. These allow fields in these tables to be referred to through the alias, which saves time and space when compared to typing the entire table name.

JOIN without a preceding qualifier is the same as INNER JOIN. I prefer to use INNER JOIN to make my intention obvious.

As far as the time span goes – I think you’d want to add a WHERE clause to the inner SELECT to limit the rows fetched to the ones for the specific time span you’re interested in.

Additional Info:

Some SQL Info:

Advertisement

Answer

Edit