mySQL sub-query causing Unknown column error

Question

I have two tables in a database. One has a list of accounts with various datapoints by account (fix_cost_summary). The second table has all trades done by all accounts and the commissions associated with the trades (comm_table). I am trying to run a query to output all columns from fix_cost_summary and a sum of commissions by account from comm_table.

My subquery is causing an "Unknown Column" error. Please let me know if there is an error in my code.

SELECT 
      fix_cost_summary.*,
     comm_table.Short_Name, 
     (SELECT SUM(Commissions) 
        FROM
             comm_table     
        GROUP BY 
             comm_table.Short_Name)
FROM
     fix_cost_summary,
     comm_table
WHERE 
     fix_cost_summary.Short_Name = comm_table.Short_name

Are you sure all columns actually exist? This seems to work. — Joachim Isaksson
– Joachim Isaksson, Commented May 16, 2013 at 18:13
@JoachimIsaksson: But as soon as there are records with multiple Short_Name values in comm_table, the subquery will return multiple results which will produce ER_TOO_MANY_ROWS (albeit not the error cited by the OP). — eggyal
– eggyal, Commented May 16, 2013 at 18:16
@eggyal Touché, I was only attempting to reproduce the error in the question. — Joachim Isaksson
– Joachim Isaksson, Commented May 16, 2013 at 18:41

eggyal · Accepted Answer · 2013-05-16 18:13:58Z

2

Assuming that Short_Name is unique in fix_cost_summary, MySQL will allow you to simply join the tables and then group on that column:

SELECT   fix_cost_summary.*, SUM(comm_table.Commissions)
FROM     fix_cost_summary JOIN comm_table USING (Short_Name)
GROUP BY Short_Name

answered May 16, 2013 at 18:13

eggyal

127k18 gold badges220 silver badges244 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Stevenyc091 Over a year ago

IF I wanted to add a WHERE clause to the SUM command, how would I do that? I want to SUM(comm_table.Commissions) for a date range comm_table.Date > X and comm_table.Date < Y

eggyal Over a year ago

@Steve: Add your WHERE clause just before the GROUP BY clause.

Stevenyc091 Over a year ago

There are some accounts listed in the fix_cost_summary table that are not in the comm_table (accounts that haven't done any commissions). I still want them to show up in the result. I made it a LEFT JOIN which I thought would solve it but it hasn't. Any suggestions?

eggyal Over a year ago

@Steve: Yes, you have the correct idea with a LEFT JOIN, but you will need to move the filter (currently in your new WHERE clause) into the JOIN criteria: change USING (Short_Name) to ON fix_cost_summary.Short_Name = comm_table.Short_Name AND comm_table.Date > X AND comm_table.Date < Y; you will then also need to qualify the reference to Short_Name with a table name in the GROUP BY clause: GROUP BY fix_cost_summary.Short_Name. Note that you can make the whole thing somewhat less verbose by aliasing the tables.

ypercubeᵀᴹ · Accepted Answer · 2013-05-16 18:23:54Z

0

The inline subquery will return more than 1 rows. One way to solve this, is to change it into a derived table:

SELECT 
      f.*,
      c.Sum_Commisions
FROM
      fix_cost_summary AS f
   JOIN
      ( SELECT Short_Name,
               SUM(Commissions) AS Sum_Commisions
        FROM
               comm_table     
        GROUP BY 
               Short_Name
      ) AS c 
         ON c.Short_Name = f.Short_Name ;

edited May 16, 2013 at 18:23

answered May 16, 2013 at 18:17

ypercubeᵀᴹ

116k19 gold badges181 silver badges249 bronze badges

2 Comments

Barmar Over a year ago

You're missing the join condition.

ypercubeᵀᴹ Over a year ago

@Barmar thnx. It didn't look right when I posted but I couldn't figure out why ...

Collectives™ on Stack Overflow

mySQL sub-query causing Unknown column error

2 Answers 2

4 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related