Problems with SUM?

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

Problems with SUM?

Sanri Parov
Hi everybody,

I'm quite new to SQL so please be patient on me... :-)
I'm doing a load/unload application for a warehouse and I've got a  
table like this


WAREHOUSE:
1 date date
2 code integer primary not null
3 goodie text
4 mu text (it's a measurement unit)
5 amount float
6 movement text
7 customer text
8 notes text


A typical series of entries is like this:

10-06-77 10 Screwdriver nr 10 buy none none
11-06-77 10 Screwdriver nr 11 buy none none
12-06-77 10 Screwdriver nr -3 sell none none
12-06-77 9 Driller nr 1 buy none none

What I'd like to do is : from a certain date to a certain date AND  
from a certain code to a certain code I'd like to have the sum of all  
the movements. in this case, if I would know how many screwdrivers I  
had from 10-06-77 to 12-06-77 the result would have been 18.
I've tried with

SELECT date, code, goodie, sum(amount) AS total FROM warehouse GROUP  
BY code HAVING (date BETWEEN '10-06-77' AND '12-06-77' AND code    
BETWEEN '10' AND '10')

The result in total is correct, but the application totally rejects  
any given starting or ending date...
What am I doing wrong?

Many thanks to all of you.

--
Sanri Parov from iBook



Reply | Threaded
Open this post in threaded view
|

Re: Problems with SUM?

Jay Sprenkle
On 11/9/05, Sanri Parov <[hidden email]> wrote:
> SELECT date, code, goodie, sum(amount) AS total FROM warehouse GROUP
> BY code HAVING (date BETWEEN '10-06-77' AND '12-06-77' AND code
> BETWEEN '10' AND '10')
>

I believe you probably want:

SELECT date, code, goodie, sum(amount) AS total
 FROM warehouse
 WHERE date BETWEEN '1977-10-06' AND '1977-12-06'
 AND code ='10'
GROUP BY code

Having is applied after the grouping
where is applied to filter results before grouping.
It will probably make little difference in this example
I think you just have a date format problem.
Reply | Threaded
Open this post in threaded view
|

Re: Problems with SUM?

Sanri Parov

Il giorno 09/dic/05, alle ore 21:29, Jay Sprenkle ha scritto:

> On 11/9/05, Sanri Parov <[hidden email]> wrote:
>> SELECT date, code, goodie, sum(amount) AS total FROM warehouse GROUP
>> BY code HAVING (date BETWEEN '10-06-77' AND '12-06-77' AND code
>> BETWEEN '10' AND '10')
>>
>
> I believe you probably want:
>
> SELECT date, code, goodie, sum(amount) AS total
>  FROM warehouse
>  WHERE date BETWEEN '1977-10-06' AND '1977-12-06'
>  AND code ='10'
> GROUP BY code
>
> Having is applied after the grouping
> where is applied to filter results before grouping.
> It will probably make little difference in this example
> I think you just have a date format problem.

Many thanks. Truly.
It solved the problem... at least it seems so !! :-))
BTW, is there a good resource to better understand HAVING and GROUP BY ?
I don't think I've fully understood the difference.

PS: date format was not a problem :-)

--
Sanri Parov from iBook



Reply | Threaded
Open this post in threaded view
|

Re: Problems with SUM?

Jay Sprenkle
> > Having is applied after the grouping
> > where is applied to filter results before grouping.
> > It will probably make little difference in this example
> > I think you just have a date format problem.
>
> Many thanks. Truly.
> It solved the problem... at least it seems so !! :-))
> BTW, is there a good resource to better understand HAVING and GROUP BY ?
> I don't think I've fully understood the difference.
>
> PS: date format was not a problem :-)

Any SQL tutorial should explain it. Did you read this one?
http://sqlite.org/lang_select.html