Home » SQL & PL/SQL » SQL & PL/SQL » How to get the total number of occurrences based on the value of a column.
How to get the total number of occurrences based on the value of a column. [message #582607] Sat, 20 April 2013 01:41 Go to next message
caloy007
Messages: 1
Registered: April 2013
Junior Member
Hello everyone,

This is the first time that I will ask question here on your forum but has been following several threads ever since. I guess that now is my turn to ask a question. So anyway here is the thing, I have a query that should return count the number of rows depending on the value of SLOT. The expected result will be like this:

WIPDATAVALUE          SLOT             N            M
1-2                   TRALTEST43S1     1            3
1-2                   TRALTEST43S1     2            3
3                     TRALTEST43S1     3            3
4-6                   TRALTEST43S2     1            4
4-6                   TRALTEST43S2     2            4
4-6                   TRALTEST43S2     3            4
7                     TRALTEST43S2     4            4


The M column is used to count the total number of occurrences of a SLOT. Now, as for the N field, this is used to count the occurrence of the SLOT. In my example for the SLOT TRALTEST43S1, it has three occurrences so M will be 3. Why 3, is because of the WIPDATAVALUE. The WIPDATAVALUE of TRALTEST43S1 is 1-2 and 3. 1-2 WIPDATAVALUE signifies two occurrences (one to two) and 3 signifies only one occurrence. As for N, it should just count the number of occurrence. To further explain, see below:
WIPDATAVALUE       SLOT                N                M
1-2                TRALTEST43S1        1                3        -> First occurrence in the total of 3
1-2                TRALTEST43S1        2                3        -> Second occurrence in the total of 3
3                  TRALTEST43S1        3                3        -> Third occurrence in the total of 3
4-6                TRALTEST43S2        1                4        -> First occurrence in the total of 4
4-6                TRALTEST43S2        2                4        -> Second occurrence in the total of 4
4-6                TRALTEST43S2        3                4        -> Third occurrence in the total of 4
7                  TRALTEST43S12       4                4        -> Fourth occurrence in the total of 4


This is the query that I have so far:

SELECT DISTINCT
WIPDATAVALUE, SLOT
, LEVEL AS n
, m 
FROM
(
  SELECT
    WIPDATAVALUE
    , SLOT
    , (dulo - una) + 1 AS m 
  FROM
  (
    SELECT 
      WIPDATAVALUE
      , SLOT
      , CASE WHEN INSTR(wipdatavalue, '-') = 0 THEN wipdatavalue ELSE SUBSTR(wipdatavalue, 1, INSTR(wipdatavalue, '-')-1) END AS una
      , CASE WHEN INSTR(wipdatavalue, '-') = 0 THEN wipdatavalue ELSE SUBSTR(wipdatavalue, INSTR(wipdatavalue, '-') + 1) END AS dulo
    FROM trprinting
    WHERE (containername = :lotID OR SLOT= :lotID) AND WIPDATAVALUE LIKE :wip
  )
) CONNECT BY LEVEL <= m
ORDER BY wipdatavalue;


I think that my current query is basing its M and N results on WIPDATAVALUE and not the SLOT that is why I get the wrong output. I have also tried to use the WITH Statement and it works well but unfortunately, our system cant accept subquery factoring.

Re: How to get the total number of occurrences based on the value of a column. [message #582609 is a reply to message #582607] Sat, 20 April 2013 02:08 Go to previous messageGo to next message
Michel Cadot
Messages: 59414
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Welcome to the forum.

Quote:
This is the query that I have so far:


The query to answer which question? And what is the actual input, everything seems to be in the current table, isn't it?

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Regards
Michel
Re: How to get the total number of occurrences based on the value of a column. [message #582889 is a reply to message #582607] Tue, 23 April 2013 04:01 Go to previous message
Barbara Boehmer
Messages: 7995
Registered: November 2002
Location: California, USA
Senior Member
Although it would help to have a test case, it looks like you have the wipdatavalue and slot columns in your table and are trying to calculate values for m and n. It looks like you could use some analytic functions like count and row_number. For example (untested due to lack of test case):

select wipdata, slot,
       row_number () over (partition by slot order by wipdatavalue) n,
       count(*) over (partition by slot) m
from   trprinting;


Previous Topic: email on job start/complete
Next Topic: what is error in below code?
Goto Forum:
  


Current Time: Tue Oct 21 15:48:54 CDT 2014

Total time taken to generate the page: 0.11497 seconds