1. Andrew Davis
  2. PowerBuilder
  3. Tuesday, 24 August 2021 07:56 AM UTC

Hi,

 

I am creating a datawindow - from a single table - i want it to group - a field - in this case number of washes, what i would like is for it to group it in say 10s

so 0-9 washes 10-19 washes etc. 

 

regards and thanks in advance 

 

Andrew

 

 

Andreas Mykonios Accepted Answer Pending Moderation
  1. Tuesday, 24 August 2021 09:46 AM UTC
  2. PowerBuilder
  3. # 1

The previous query with your data. 

SELECT Cast(Cast(( CASE WHEN washes = 0 THEN washes ELSE washes - 1 END ) / 10 AS SMALLINT) * 10 + 1 AS VARCHAR(10)) + '-' + Cast(Cast(( CASE WHEN washes = 0 THEN washes ELSE washes - 1 END ) / 10 AS SMALLINT) * 10 + 10 AS VARCHAR(10)) grouping_descr,
       material,
       Cast(( CASE
                WHEN washes = 0
                THEN washes
                ELSE washes - 1
              END ) / 10 AS SMALLINT)                                                                                                                                                                                                       AS grouping,
       Count(washes)                                                                                                                                                                                                                        AS counter
  FROM (SELECT 110011  AS rfid,
               'Towel' AS material,
               20      AS washes
        UNION
        SELECT 110122  AS rfid,
               'Towel' AS material,
               15      AS washes
        UNION
        SELECT 110511  AS rfid,
               'Towel' AS material,
               16      AS washes
        UNION
        SELECT 210121  AS rfid,
               'Sheet' AS material,
               11      AS washes
        UNION
        SELECT 110013  AS rfid,
               'Towel' AS material,
               5       AS washes
        UNION
        SELECT 110018  AS rfid,
               'Towel' AS material,
               6       AS washes
        UNION
        SELECT 210122  AS rfid,
               'Sheet' AS material,
               3       AS washes
        UNION
        SELECT 210155  AS rfid,
               'Sheet' AS material,
               12      AS washes) AS v1
 GROUP BY material,
          Cast(( CASE
                   WHEN washes = 0
                   THEN washes
                   ELSE washes - 1
                 END ) / 10 AS SMALLINT)
 ORDER BY material 

If the column names are the same you would replace what is in the from with your table name. Of course depending on the sql server you use you may need to adjust the syntax.

These is the result I get:

grouping_descr material grouping counter
1-10 Sheet 0 1
11-20 Sheet 1 2
1-10 Towel 0 2
11-20 Towel 1 3

 

I made some modifications to conform with grouping in ranges of 1-10 instead of 0-9.

Andreas.

Comment
There are no comments made yet.
Andrew Davis Accepted Answer Pending Moderation
  1. Tuesday, 24 August 2021 09:23 AM UTC
  2. PowerBuilder
  3. # 2

So example data would be

table 

rfid desc washes

110011 Towel 20

110122 Towel 15

110511 Towel 16

210121 Sheet 11

110013 Towel 5

110018 Towel 6

210122 Sheet 3

210155 Sheet 12

 

Towel

1-10 washes        2

11-20 Washes     3

 

Sheet

1-10 washes 1

11-20 washes 2

 

Comment
There are no comments made yet.
René Ullrich Accepted Answer Pending Moderation
  1. Tuesday, 24 August 2021 08:36 AM UTC
  2. PowerBuilder
  3. # 3

Could you provide some example data and the result you expect?

Comment
There are no comments made yet.
Andreas Mykonios Accepted Answer Pending Moderation
  1. Tuesday, 24 August 2021 08:35 AM UTC
  2. PowerBuilder
  3. # 4

Hi.

This can be accomplished in your sql statement. See the following example (I don't use any table - I don't know your schema - the data are in the inner sql statements with union):

SELECT cast(washes / 10 as smallint) as group_washes, count(washes)
  FROM (SELECT 0 AS washes
        UNION
	SELECT 1 AS washes
        UNION
        SELECT 2 AS washes
        UNION
        SELECT 3 AS washes
        UNION
        SELECT 4 AS washes
        UNION
        SELECT 5 AS washes
        UNION
        SELECT 6 AS washes
        UNION
        SELECT 7 AS washes
        UNION
        SELECT 8 AS washes
        UNION
        SELECT 9 AS washes
        UNION
        SELECT 10 AS washes
        UNION
        SELECT 11 AS washes
        UNION
        SELECT 12 AS washes
        UNION
        SELECT 13 AS washes
        UNION
        SELECT 14 AS washes
        UNION
        SELECT 15 AS washes
        UNION
        SELECT 16 AS washes
        UNION
        SELECT 17 AS washes
        UNION
        SELECT 18 AS washes
        UNION
        SELECT 19 AS washes
        UNION
        SELECT 20 AS washes
        UNION
        SELECT 21) AS v1 
group by cast(washes / 10 as smallint)
order by cast(washes / 10 as smallint)

The result I get is:

group_washes count(v1.washes)
0 10
1 10
2 2

Andreas.

Comment
  1. René Ullrich
  2. Tuesday, 24 August 2021 08:38 AM UTC
Or do the same calculation in the datawindow group with

int(washes / 10)
  1. Helpful
  1. Andreas Mykonios
  2. Tuesday, 24 August 2021 08:52 AM UTC
René, you are right. Depending on the resulting rows there is a decision that should be made, if we want the processing to be done in the server or in the client.
  1. Helpful
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.
We use cookies which are necessary for the proper functioning of our websites. We also use cookies to analyze our traffic, improve your experience and provide social media features. If you continue to use this site, you consent to our use of cookies.