SQLException: UCAExc:::5.0.0 row column count mismatch

Question

I have a project created by java code, and a database that has two tables (table1 and table2) looks like this:

-------table1--------

Name      |  Ref
__________|__________
    A     |  100
    B     |  200
__________|__________

and 

-------table2--------

Name      |  Q
__________|__________
    A     |  12
    B     |  10
    A     |  14
__________|__________

I try create a SQL query to get this result:

Name      |  Ref    |  SUM(Q)
__________|_________|__________
    A     |  100    |   26
    B     |  200    |   10
__________|_________|__________

I wrote this query

query ="SELECT table1.Name,table1.Ref FROM table1 WHERE table1.Name=(SELECT table2.Name,SUM(table2.Q) FROM table2 GROUP BY table2.Name)";

but my code not working and I'm getting this error:

net.ucanaccess.jdbc.UcanaccessSQLException: UCAExc:::5.0.0 row column count mismatch

any suggestions to correct my query code?

**Please do not change your question after it has been answered.** Instead please ask *another question* if you need help with something else. — O. Jones, Sep 10 '21 at 18:50

score 0 · Answer 1 · answered Sep 10 '21 at 14:03

0

Use this:

SELECT table1.Name,table1.Ref 
  FROM table1 
 WHERE table1.Name IN (SELECT table2.Name FROM table2)

Your subquery has two problems.

It returns more than one column, so it's not suitable for matching with = or IN.
It may return more than one row, so it's not suitable for matching with =.

answered Sep 10 '21 at 14:03

O. Jones

103,626
17
118
172

but I need to show a result of ```SUM(table2.Q)``` how can I do it ?? – waroa kawa Sep 10 '21 at 14:31
Please ask another question. And, in the question please show some sample data and desired results from that sample data. – O. Jones Sep 10 '21 at 15:38
sorry I think the pbm in my code java not in query, I will try to fix it – waroa kawa Sep 10 '21 at 16:24

SQLException: UCAExc:::5.0.0 row column count mismatch

1 Answers1