Re: Frequency distribution SQL statement

From: Steve (sedmyer_at_indy.rr.com)
Date: 02/04/05 

Date: Fri, 04 Feb 2005 18:19:53 GMT

Dean,

This looks like what I need. Could you help me to write this as a stored
procedure that would take the min value (zero in your sample SQL), the max
value (30 in your sample SQL) and the increment (10 in your sample) as
parameters.

thank you much for your help
Steve

"Dean" <deanv@nospam.spin.hr> wrote in message
news:etW$SAuCFHA.2296@TK2MSFTNGP15.phx.gbl...
> something like this?
>
> SELECT range, COUNT(attrib_value) AS TOTAL
> FROM
> (SELECT
> CASE
> WHEN attrib_value BETWEEN 0 AND 10 THEN '0-10'
> WHEN attrib_value BETWEEN 10 AND 20 THEN '10-20'
> WHEN attrib_value BETWEEN 20 AND 30 THEN '20-30'
> ELSE 'else'
> END AS range,
> attrib_value
> FROM t
> WHERE attrib_name = 'Age') a
> GROUP BY range
> ORDER BY TOTAL DESC
>
> dean
>
> "Steve" <sedmyer@indy.rr.com> wrote in message
> news:jlNMd.5365$4x5.2854@fe2.columbus.rr.com...
>> OOPS, did not complete my response before sending. Let me try again.
>> Comments inline
>>
>> "David Gugick" <davidg-nospam@imceda.com> wrote in message
>> news:ufCrJPtCFHA.1408@TK2MSFTNGP10.phx.gbl...
>> > SELECT attrib_value, COUNT(attrib_value) AS TOTAL
>> > FROM MyTable
>> > WHERE attrib_name = 'Age'
>> > and (attrib_value >=20 and attrib_value <=25)
>>
>> This will work for the single range but what I need is to get many ranges
>> from a single SQL statement. So the result would indicate that age = 20
> to
>> 25 has a count of x, age = 25 to 30 has count of y, age = 30 to 35 has
>> count of z, etc. Further I would like the Max, Min and increment values
> (in
>> the previous example min = 20 max = 35 and increment = 5) to be
>> parameters
>> of the query. Hummm, as I say that it seems that what I really need here
> is
>> a stored procedure. If some one could help me with that it would be
> great.
>> I am a VB programmer and am not real sure as to how to go about coding
>> stored procedures
>>
>> > GROUP BY attrib_name -- shouldn't this be ATTRIB_NAME???
>>
>> I do not think so...the where clause will ensure that only the
>> attrib_name
>> 'Age' will be returned (no need to group by a single value) and the idea
> is
>> to group the results by the frequency of occurance of a specific age
>> value
>>
>>
>> >
>> >
>> > --
>> > David Gugick
>> > Imceda Software
>> > www.imceda.com
>>
>>
>
>

Relevant Pages

  • Re: Leading zero for Default Number
    ... auto number to increment it. ... I'm supposed to place the zero and the 7. ... the exact same number* - stored as 00000000000000000000000000000111 to be ... it can be done with some VBA code - but it gets complicated when you ...
    (microsoft.public.access.tablesdbdesign)
  • Re: IF statement query
    ... I realised that I had the criteria back to front. ... every increment of 30 under 120 the formula should add 10. ... Negative values in A1 are treated the same as zero, ... I would like to look at cell A1 and if that cell contains a number ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Application.Min - how to ignore zero value?
    ... but I believe this is saying: "If there is no zero ... increment down with each loop. ... max and/or the standard deviation? ...
    (microsoft.public.excel.programming)
  • Re: Need someone very familiar with arrays
    ... zero to where those elements were moved from or the interior start loop will not know when to stop. ... It would in effect move until it reached the last non zero item in the list and then write that value repeatedly whenever a zero was encountered. ... Once you put the test in, then start will never increment past finish, so there is never any concern for what lies higher up than finish. ...
    (microsoft.public.vb.general.discussion)