You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@openoffice.apache.org by John Meyer <jo...@pueblocomputing.com> on 2013/10/20 20:03:15 UTC

SQL problem

Relevant tables and fields:

Agents
TSR Varchar(6)

Sales
SalesRep Varchar(6)

neither is a primary key (I'm having problems converting it to a primary 
key when I import the data from a spread sheet).

I want to find out how many sales each agent got.


Statement:
     SELECT "Agents"."TSR", "Agents"."Agent", COUNT(*) FROM "Agents" 
INNER JOIN "Sales" ON "Agents"."TSR" = "Sales"."Sales Rep"

Gives me a not in aggregate function or group by clause.

Any ideas while I search for the answer?

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@openoffice.apache.org
For additional commands, e-mail: users-help@openoffice.apache.org

Re: SQL problem (SOLVED)

Posted by John Meyer <jo...@pueblocomputing.com>.
On 10/20/2013 1:14 PM, John Meyer wrote:
> Semi-solved, I think.
>
> I managed to join the following:
>
> SELECT "AGENTS"."AGENT_ID", COUNT(*) AS "Total Sales" FROM "AGENTS" 
> INNER JOIN "SALES" ON "AGENTS"."AGENT_ID"="SALES"."SALES_REP" GROUP BY 
> "AGENTS"."AGENT_ID"
>
> The only problem is I can't seem to add the agent's name.
>
>
I ended up solving the problem by breaking up the query into two 
subqueries: one to count the results and the other to join the 
information with the name.  It isn't the most elegant solution int he 
world, but it works!

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@openoffice.apache.org
For additional commands, e-mail: users-help@openoffice.apache.org

Re: SQL problem

Posted by John Meyer <jo...@pueblocomputing.com>.
Semi-solved, I think.

I managed to join the following:

SELECT "AGENTS"."AGENT_ID", COUNT(*) AS "Total Sales" FROM "AGENTS" 
INNER JOIN "SALES" ON "AGENTS"."AGENT_ID"="SALES"."SALES_REP" GROUP BY 
"AGENTS"."AGENT_ID"

The only problem is I can't seem to add the agent's name.



---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@openoffice.apache.org
For additional commands, e-mail: users-help@openoffice.apache.org