Need assistance debugging this query

[pankajit09]

Whats wrong in the following query -->

SELECT SUM(bookings),SUM(invoices),SUM(segments),SUM(totalgross),SUM(totalsale),SUM(commission),SUM(tax),SUM(SELECT grossfare FROM salesqww where type="cash adjustments" and aircode="UA") FROM summary WHERE aircode='UA'

[ronverdonk]

Your thread title should be a synopsis of the problem you have.

Instead of letting our members solve a puzzle, what is the problem you have with the statement?

Ronald :cool:

[pankajit09]

Your thread title should be a synopsis of the problem you have.

Instead of letting our members solve a puzzle, what is the problem you have with the statement?

Ronald :cool:

I have nested a query inside the SUM .

I want to take the SUM of the column returned by that query.
Is it possible ?

[ronverdonk]

I have nested a query inside the SUM .

I want to take the SUM of the column returned by that query.
Is it possible ?

I know what you want to do!
I want to know what error is returned by MySQL!

Ronald :cool:

[Atli]

Hi.

First of all let me congratulate you on the least usefull discussion name I've seen in my time here.

That said, I'd try replacing all double quote marks " with single quote marks '

[pankajit09]

Hi.

First of all let me congratulate you on the least usefull discussion name I've seen in my time here.

That said, I'd try replacing all double quote marks " with single quote marks '

I didn't get you .

May be I got your first line but not the second.

[pankajit09]

I know what you want to do!
I want to know what error is returned by MySQL!

Ronald :cool:

The error which comes is to use to use the RIGHT syntax near the nested query.

[Atli]

Sub-queries need to be inside (), and altho your Query is inside a pair of those, that particular pair belongs to the SUM() function.

So adding a second () arround your sub-query might fix the problem.

O yea and what I said before apears to be wrong, I was under the impression that MySQL would slap you in the head for doing "this", rather than 'this'... but apparently it is possible now.
Would be good practice tho to pick one of them and stick to it.

[pankajit09]

Sub-queries need to be inside (), and altho your Query is inside a pair of those, that particular pair belongs to the SUM() function.

So adding a second () arround your sub-query might fix the problem.

I added a second () but it doesn't work.

[Shounak Joshi]

Can you give me your table structure

[Atli]

I think I may have guessed the probblem.

The sub-querie you nested inside the SUM() function returns multiple rows. Sub-queryies (as far as I've seen) may only return one row.

So I belive moving your sub-query out of the SUM function, while placing a SUM function inside your sub-query will give you the results you want.

Somewhat like this: (I reformatted the query to better understand it)

SELECT 
  SUM(bookings),
  SUM(invoices),
  SUM(segments),
  SUM(totalgross),
  SUM(totalsale),
  SUM(commission),
  SUM(tax),
  ( SELECT 
      SUM(grossfare) 
    FROM 
      salesqww 
    WHERE 
      type="cash adjustments" 
    AND 
      aircode="UA"
  ) 
FROM 
  summary 
WHERE 
  aircode='UA'
 

P.S. This had been easilly spotted by myself and many of our friends here if you had added the error message in with your post.
So please, if this does not fix it, give us error messages. We like error messages :)

[pankajit09]

I think I may have guessed the probblem.

The sub-querie you nested inside the SUM() function returns multiple rows. Sub-queryies (as far as I've seen) may only return one row.

So I belive moving your sub-query out of the SUM function, while placing a SUM function inside your sub-query will give you the results you want.

Somewhat like this: (I reformatted the query to better understand it)

SELECT 
  SUM(bookings),
  SUM(invoices),
  SUM(segments),
  SUM(totalgross),
  SUM(totalsale),
  SUM(commission),
  SUM(tax),
  ( SELECT 
      SUM(grossfare) 
    FROM 
      salesqww 
    WHERE 
      type="cash adjustments" 
    AND 
      aircode="UA"
  ) 
FROM 
  summary 
WHERE 
  aircode='UA'
 

P.S. This had been easilly spotted by myself and many of our friends here if you had added the error message in with your post.
So please, if this does not fix it, give us error messages. We like error messages :)

So which will be more efficient -->

The above query which you mentioned

OR

Two seperate queries ?

[Atli]

I'd reccomend the sub-query.
Making two queries will require more code in you application and you will have to query the database twice.

[pankajit09]

I'd reccomend the sub-query.
Making two queries will require more code in you application and you will have to query the database twice.

I don't think the sub query is fast.

I tested it and found out that its slow compared to two seperated queries.