0

I want to get sum of TotAmount from unique SONumbers. So I used this code. But it gives me the error:

Every derived table must have its own alias

Please help me to solve this.

My code:

ResultSet rs = s.executeQuery ("SELECT TotAmount 
                               From (SELECT Distinct SONumber From sale) 
                               where DateTime between '" + FromDate + " 
00:00:00" + "' AND '" + ToDate  + " 00:00:00" + "' ");
Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
Nuwan Dissanayaka
  • 131
  • 2
  • 11

2 Answers2

1

Every derived table (subquery) should have an alias, which you can give using AS:

... From (SELECT Distinct SONumber From sale) where ...

Provide an alias for the subquery

... From (SELECT Distinct SONumber From sale) AS some_alias where ...
Jomoos
  • 12,823
  • 10
  • 55
  • 92
0

All you need is to add an alias to your subquery like:

ResultSet rs = s.executeQuery ("SELECT sum(TotAmount) 
                               From (SELECT Distinct TotAmount,DateTime,SONumber From sale) t
                               where DateTime between '" + FromDate + " 
                                00:00:00" + "' AND '" + ToDate  + " 00:00:00");

Here, (SELECT Distinct SONumber From sale) is now your sub-table named t. Note that t is an alias here.

However, this also will not work because there is no TotAmount and Datetime columns returning from your sub-table. You need to add those columns as well, or use a join and connect to the table where those columns are existing.

Eray Balkanli
  • 7,752
  • 11
  • 48
  • 82
  • Thank you very mush. but there is no tables to get values. I want get values from one table. Those TotAmount and SONumber is located in same table. I need to get sum of TotAmount for unique SONumbers. – Nuwan Dissanayaka Feb 15 '18 at 16:27
  • Then you need to add those columns in your subtable and use "sum" keyword. Updated my answer. – Eray Balkanli Feb 15 '18 at 16:31
  • Can you please show me how to do that using my table. Table Name = sale , Columns TotAmount and SONumber. DateTime is not a problem. I Need Sum of TotAmount of Distinct SONumber FROM sale table – Nuwan Dissanayaka Feb 15 '18 at 17:06