Sales Figures Query

[audiopro]

I am using the following query to get the total sales within a given period.

SELECT code,sum(qty),each,sum(qty)*each FROM sales where DATED>=1136160000 and DATED<1136419200 GROUP BY 'code' LIMIT 0, 100

The query works fine but I need to filter out some of the records.
I have entries which are:-
PAID_IN
PAID_OUT
NSI - (Non Stock Item)
These are different values for each instance but the query uses the same value for each instance in the table. I still need these values reporting but is there a way of handling these within one query?


Keith

http://www.studiosoft.co.uk

 

[r937]

These are different values for each instance but the query uses the same value for each instance in the table.

huh?

r937.com | rudy.ca

 

[audiopro]

Sorry if I didn't expalin it too well.
Say I have the following values of PAID_OUT amongst all the other code values.

code     | each   | Qty
PAID_OUT | 300.56 | 1
A123     |   2.30 | 4
A123     |   2.30 | 1
PAID_OUT | 230.45 | 1
A123     |   2.30 | 2
PAID_OUT | 125.74 | 1

The Total quantity and total price for all instances of A123 are calculated correctly as the value of A123 is always the same at £2.30. The values of PAID_OUT are all different but the query treats them as if they all have the same value ie. £300.56 * 3, which clearly is not what I require.
I am currently running seperate queries to calculate these values but wondered if they could be processed all at once.

Keith

http://www.studiosoft.co.uk

 

[r937]

try GROUP BY `code`, not GROUP BY 'code'

r937.com | rudy.ca

 

[audiopro]

Thanks but makes no difference to the problem. The query is taking the first value it finds for PAID_OUT and using that for every calculation.
I will keep it as it is for now but was just trying to tidy things up a bit.

Keith

http://www.studiosoft.co.uk

 

[r937]

oh, of course it is, i should've seen that the first time around

you are suffering the unpredictable results which derive from using mysql's "group by with hidden fields" (do a search on the mysql.com site for more information)

i think it's a mis-feature, and it will produce syntax errors in every other database

in any case, i don't think you should include the `each` column in the SELECT (that's what's causing the unpredictable results)

try this --

select code
     , sum(qty)
     , sum(qty*each) as net
  from sales 
 where DATED>=1136160000 
   and DATED<1136419200 
group 
    by code

r937.com | rudy.ca