Solved

grouping data by days

Posted on 2014-12-24
42
44 Views
Last Modified: 2015-11-29
i have table cr_gnditem consist of the following items of sales transactions
unit number  ( = store number )
dob date  ( transaction date )
checkid number ( invoice number )
revid number
modid number
periodid number
price number ( the amount )

now , look at this query
select unit,dob,modeid,period,revid,count(checkid),sum(amount) from
(select unit,dob,modeid,period,revid,checkid,sum(price) Amount
 from cr_gnditem
where dob = '01-jan-14' and unit = '5' 
 group by unit,dob,modeid,period,revid,checkid)
 group by unit,dob,modeid,period,revid;

Open in new window


that query result like this
unit	dob	modeid	period	revid	checkCount	Amont
5	01-01-2014	3	3	3	4	115
5	01-01-2014	5	4	1	39	1050
5	01-01-2014	1	4	1	41	2195
5	01-01-2014	1	3	1	5	125
5	01-01-2014	3	4	3	94	4056
5	01-01-2014	5	3	1	7	186

Open in new window


now , i want to select from that query :
all units , modeid,period,revid,count(checkid),sum(amount)
plus how many dob  in specific period
note :
not all units has transactions in the specific period , so the code should count number of days
0
Comment
Question by:NiceMan331
  • 22
  • 7
  • 6
  • +3
42 Comments
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 40516129
May be it would be easy to understand if you could post the output what you need from the above output you already pasted.
0
 

Author Comment

by:NiceMan331
ID: 40516170
with regardless of figures value ( just demo)
unit	dob	modeid	period	revid	checkCount	Amont
5	45	3      	3	       3	      4	115
6	62	5	        4	      1	    39	1050
7	38	1	        4	      1	    41	2195
8	66	1	        3	      1	      5	125
9	43	3	       4	      3	     94	4056
10	82	5	       3	      1	      7	186

Open in new window

0
 
LVL 2

Assisted Solution

by:Pratik Makwana
Pratik Makwana earned 50 total points
ID: 40516435
select unit,count(dob),modeid,period,revid,count(checkid),sum(amount) from
(select unit,dob,modeid,period,revid,checkid,sum(price) Amount
 from cr_gnditem
 where period = '5'
 group by unit,dob,modeid,period,revid,checkid)
 group by unit,dob,modeid,period,revid;
0
 

Author Comment

by:NiceMan331
ID: 40516468
This will not correct because if you see my 1st result , if we just count dob it will give count of transaction numbers by that date while we need count of dob group by unit, dob
0
 
LVL 31

Expert Comment

by:awking00
ID: 40516696
Without actual data, it's hard to test but perhaps something like the following:
select unit,sum(dob),modeid,period,revid,count(checkid),sum(amount) from
(select unit,count(dob),modeid,period,revid,checkid,sum(price) Amount
 from cr_gnditem
 where period = '5'
 group by unit,modeid,period,revid,checkid)
 group by unit,modeid,period,revid;
0
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 40516907
Difficult without test data, but what about this:
  SELECT Unit
       , COUNT ( DISTINCT Dob ) Dob_Days
       , Modeid
       , Period
       , Revid
       , COUNT ( Checkid )
       , SUM ( Amount )
    FROM Cr_Gnditem
   WHERE Period = '5'
GROUP BY Unit
       , Modeid
       , Period
       , Revid;

Open in new window

0
 

Author Comment

by:NiceMan331
ID: 40518309
here are sample of Data
look at sheet  (input)
sample data for one unit  only
now look at sheet Output
1- i created one function name (Season) , this function return season number
i divided the year into many seasons , each season started data and ended date
suppose here season number 6 , started between Aug10-2014 Ended Aug20-2014
2-in first result ( just to explain ) , it return values every day by check value
3-in the final result , it return summary by season , and look the last cloumn ( how many days in season number 6 ) only 4 days working here
hope it is clear for all
Test.xlsx
0
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 40518503
There is no period = '5' in your sample data,
0
 

Author Comment

by:NiceMan331
ID: 40518512
there is no condition like this in my question
it is just confused by you , in my 1st post i select a sample where unit = 5 , not period = 5
just in purpose to reduce time of query
but in general , i don't need any condition , even for units
0
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 40518519
Your requirements are not clear, Pratik and awking00 also got confused with period = 5
Also:
. . . I created one function name (Season) , this function return season number
how does the DOB column relate to the season? how did  you compute that season 6 has 4 days?
0
 

Author Comment

by:NiceMan331
ID: 40518533
this is copy of the query in my 1st post
select unit,dob,modeid,period,revid,count(checkid),sum(amount) from
(select unit,dob,modeid,period,revid,checkid,sum(price) Amount
 from cr_gnditem
where dob = '01-jan-14' and unit = '5' 
 group by unit,dob,modeid,period,revid,checkid)
 group by unit,dob,modeid,period,revid;

Open in new window


i don't know from where they bring period = 5
0
 

Author Comment

by:NiceMan331
ID: 40518538
how does the DOB column relate to the season? how did  you compute that season 6 has 4 days?

you don't need to do any thing in this matter , i already created the function , i just use : get_season(dob) , it will return the correct season
i defined another table having
season number
date_from date
date_to date

the function loops dob to return season number
0
 
LVL 29

Assisted Solution

by:MikeOM_DBA
MikeOM_DBA earned 100 total points
ID: 40518559
Well, then you can use your function to compute the "CountOfDays in period Season 1" and the "Average", the rest you can get like this:
  SELECT Unit
       , Season
       , Modeid
       , Period
       , Revid
       , COUNT ( Checkid ) Countofchk
       , SUM ( Price ) Sumofprice
       , COUNT ( Dob ) Daysinseason --<<< Get with your functions
       , AVG ( Price ) Averageprice  --<<< compute based on your function
    FROM Cr_Gnditem
   WHERE 1 = 1               --Period = '5'
GROUP BY Unit
       , Season
       , Modeid
       , Period
       , Revid
ORDER BY 1, 2, 3;

Open in new window

0
 

Author Comment

by:NiceMan331
ID: 40518638
1- count checkid will return count of all transactions , if you review my input sheet , you will see that each check had many transactions , while I need count of check number only                                              2- you mean I have to create another function to return count of days within season ?
0
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 40518666
The only data not returned by the query I posted is: "CountOfDays in period Season 1" and the "Average" (which seems to depend of the count of days.
Apart from mentioning some Function, you have provided no information on how to compute this "CountOfDays in period Season 1" and therefore the "Average",
Apart from these columns, the rest of the columns agree with your expected results.
0
 

Author Comment

by:NiceMan331
ID: 40518907
you have provided no information on how to compute this "CountOfDays in period Season 1" and therefore the "Average",

when the function get_season(dob) return season number for each dob
simply look for the input data , you will find that those transactions stand within season number 6 ( as an example)
then we will find 4 days only in the query > 15 ,16,17 and 18 aug 2014
season no 6 is between 10->20 aug 2014 , but we find only 4 distinct days
0
 

Author Comment

by:NiceMan331
ID: 40518915
again , please look for the output data
i need the ditinct count of check and the sum for each check
because just count(check) in my data will return 34 count of check , while the correct number is just 12
0
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 40522054
Again, you are the only one who knows how to use your mystery function to obtain that data.
Apart from a crystal ball, we have no way of to even guess what it does.
0
 

Author Comment

by:NiceMan331
ID: 40522085
mike
you asked me about sample data , i send it to you , it is very clear
my request is very clear
just look for the spreadsheet , what is the input , what is the output , you will easily know what i need
0
 
LVL 31

Assisted Solution

by:awking00
awking00 earned 150 total points
ID: 40522394
Note - I used a case statement for the season since I don't have access to your get_season function. Also, since you only provided one unit number and dob within one season, some of the subquerys and analytic functions may need additional group by and/or partition by statements. But given your input the enclosed query produces these results:
      UNIT     SEASON     MODEID   PERIODID      REVID   CNTOFCHK SUMOFPRICE DAYSOFSEASON        AVG

---------- ---------- ---------- ---------- ---------- ---------- ---------- ------------ ----------

         1          6          1          2          3          2         29            4       7.25

         1          6          2          1          2          2         79            4      19.75

         1          6          2          2          1          1         11            4       2.75

         1          6          2          2          2          1         23            4       5.75

         1          6          2          3          1          1         23            4       5.75

         1          6          3          2          1          1         57            4      14.25

         1          6          4          3          2          1         17            4       4.25

         1          6          5          3          2          1         12            4          3

         1          6          5          4          3          1         35            4       8.75

         1          6          6          3          2          1         15            4       3.75

select distinct unit, season, modeid, periodid, revid, cntofchk, sumofprice,
last_value(days) over (order by unit) daysofseason,
sumofprice/last_value(days) over (order by unit) avg from
(select unit,
 count(*) over (partition by dob order by unit) days,
 case when dob between to_date('20140810','yyyymmdd') and to_date('20140820','yyyymmdd')
      then 6
 end as season,
 modeid, periodid, revid,
 count(price) over (partition by unit, modeid, periodid, revid) cntofchk,
 sum(price) over (partition by unit, modeid, periodid, revid) sumofprice
 from
 (select unit, dob, modeid, periodid, revid, checkid,
  sum(price) price
  from cr_gnditem
  group by unit, dob, modeid, periodid, revid, checkid
  order by unit, dob, modeid, periodid, revid, checkid))
order by modeid, periodid, revid;

Open in new window

0
 

Author Comment

by:NiceMan331
ID: 40522418
Yes waking , I think this will work when I see your logic by using over partition , but I'm now out of office , let me try it tomorrow , thanx
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:NiceMan331
ID: 40523269
awking
thanx for your query , but as you said it requier some grouping & partioning

here are my query
select t.unit,get_seas(dob) season,modeid,period,count(checkid) Cust_Cnt,sum(amount) Amount
from

(select unit,dob,modeid,period,checkid,sum(price) Amount
 from cr_gnditem 
 group by unit,dob,modeid,period,checkid) 
 group by unit,get_seas(dob),modeid,period;

Open in new window


every thing is ok for the above , except count of days inside unit / season
if you can please just add how to partion it correctly
i added like this :
count (*) OVER (PARTITION BY unit, season) Days,
but it not gives correct result
in other word , i want :
select count(dob) from cr_gnditem group by unit,season
0
 
LVL 31

Expert Comment

by:awking00
ID: 40523542
Perhaps you can supply some additional data covering multiple units and dobs from multiple seasons (a couple of each would suffice) and the expected output. It may also be helpful to post your get_season function as well.
0
 

Author Comment

by:NiceMan331
ID: 40523564
Ok , good idea also , thanx
0
 

Author Comment

by:NiceMan331
ID: 40524039
i tried to extend the sample , but the data is too large
i draw flow chart , i dont know if this will help
1- i have table season ,
id number
f_date date
t_date date

and the function like this

CREATE OR REPLACE FUnCTION get_seas(td date) return number is
    
   ser number(10);
 BEGIN
 select id into ser from season where f_date <= td and t_date >= td;
    return ser;
    EXCEPTION
   WHEN NO_DATA_FOUND THEN
     RETURN 0;
    end get_seas;

Open in new window



in my chart , look the the left side , here is just how the function calculate season number
then , look to the middle , we found in season no 6 ( which is between aug 10 till aug 20 )
we found only 5 days having  transactions ( for 1 unit )
and for each day we have many checks
for example day aug 10 , we found  4 checks
each check also consist of many transactions to calculate its value
for example check # 05 like this :
item A    amount  = 3
item B   amount  =  2
item c  amount  =  5

total value of the check = 3 + 2 + 5 = 10

the expected output will be
unit  - season - count of days - count of checks - sum(amount)
1            6                  5                         20                        1000
2            7                   6                         25                         1500

Open in new window


expecting we have  total 20 checks in the 5 days of season 6
and 25 checks in the 6 days of season # 07
hope this will be fine
Season.pdf
0
 
LVL 31

Expert Comment

by:awking00
ID: 40524107
Are you saying that there is only one unit per season? Exactly what does a unit represent?
0
 

Author Comment

by:NiceMan331
ID: 40524141
NO , they are many units
unit = branch number
i just explained transactions of one unit , but the table has transactions of all units
and here is a content of the table as per my 1st post
unit number  ( = store number )
dob date  ( transaction date )
checkid number ( invoice number )
revid number
modid number
periodid number
price number ( the amount )
0
 
LVL 31

Expert Comment

by:awking00
ID: 40524174
I'm not too much concerned with the get_season function as I can re-create something similar for testing (although my test database is down at the moment), but I guess I was a little confused by this -
unit  - season - count of days - count of checks - sum(amount)
1            6                  5                         20                        1000
2            7                   6                         25                         1500

Open in new window

Could there not also be something like this? -
unit  - season - count of days - count of checks - sum(amount)
1            6                  5                         20                        1000
1            7                  4                         15                        1100
2            6                   3                         12                         500
2            7                   6                         25                         1500

Open in new window

0
 
LVL 31

Expert Comment

by:awking00
ID: 40524266
I have tried to expand your test.xlsx data, adding another unit and another "season" in the attached file. Are those numbers valid for your situation? If so, can you determine the output from them that you would expect? If not, can you modify them in a way that matches your issue and show that output?
0
 
LVL 31

Expert Comment

by:awking00
ID: 40524268
Forgot the attached file :-(
ExpandedTest.xlsx
0
 

Author Comment

by:NiceMan331
ID: 40524355
Yes correct , the output will be like this :        Unit -  season - count of days.                           1.   -    6. -          4.                                                 2. -       6 -           3                                                 1.  -      7   -         4.                                                2 -        7 -           6
0
 

Author Comment

by:NiceMan331
ID: 40524362
Unit 1 in season 6 has 4 days : 15-16-17-18.    And unit 2 has 3 days :  15-16-18.          Then in season 7 , unit 1 had 4 days : 22-24-25-27. And unit 2 had 6 days :  22-23-24-25-26/27
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 41025846
forgive me if this doesn't help, but I refer back to the firt set of sample data and the 2 outputs in that spreadsheet.

For this test (dummy) function and sample data:
CREATE OR REPLACE FUNCTION season(any_dt date) RETURN INT IS
 
BEGIN
  RETURN 6;
END season;
/
 
 
CREATE TABLE Table1
    (UNIT int, DOB date, MODEID int, PERIODID int, REVID int, "CHECK" int, ITEM varchar2(1), PRICE int)
/

INSERT ALL 
    INTO Table1 ("UNIT", "DOB", "MODEID", "PERIODID", "REVID", "CHECK", "ITEM", "PRICE")
         VALUES (1, to_date('2014-08-15','yyyy-mm-dd'), 1, 2, 3, 1002, 'A', 10)
    INTO Table1 ("UNIT", "DOB", "MODEID", "PERIODID", "REVID", "CHECK", "ITEM", "PRICE")
         VALUES (1, to_date('2014-08-15','yyyy-mm-dd'), 1, 2, 3, 1002, 'B', 8)
    INTO Table1 ("UNIT", "DOB", "MODEID", "PERIODID", "REVID", "CHECK", "ITEM", "PRICE")
         VALUES (1, to_date('2014-08-15','yyyy-mm-dd'), 1, 2, 3, 1002, 'C', 7)
    INTO Table1 ("UNIT", "DOB", "MODEID", "PERIODID", "REVID", "CHECK", "ITEM", "PRICE")
         VALUES (1, to_date('2014-08-15','yyyy-mm-dd'), 2, 1, 2, 1003, 'A', 6)
    INTO Table1 ("UNIT", "DOB", "MODEID", "PERIODID", "REVID", "CHECK", "ITEM", "PRICE")
         VALUES (1, to_date('2014-08-15','yyyy-mm-dd'), 2, 1, 2, 1003, 'D', 8)
    INTO Table1 ("UNIT", "DOB", "MODEID", "PERIODID", "REVID", "CHECK", "ITEM", "PRICE")
         VALUES (1, to_date('2014-08-15','yyyy-mm-dd'), 2, 1, 2, 1003, 'M', 5)
    INTO Table1 ("UNIT", "DOB", "MODEID", "PERIODID", "REVID", "CHECK", "ITEM", "PRICE")
         VALUES (1, to_date('2014-08-15','yyyy-mm-dd'), 2, 1, 2, 1003, 'K', 6)
    INTO Table1 ("UNIT", "DOB", "MODEID", "PERIODID", "REVID", "CHECK", "ITEM", "PRICE")
         VALUES (1, to_date('2014-08-15','yyyy-mm-dd'), 4, 3, 2, 1004, 'L', 4)
    INTO Table1 ("UNIT", "DOB", "MODEID", "PERIODID", "REVID", "CHECK", "ITEM", "PRICE")
         VALUES (1, to_date('2014-08-15','yyyy-mm-dd'), 4, 3, 2, 1004, 'J', 8)
    INTO Table1 ("UNIT", "DOB", "MODEID", "PERIODID", "REVID", "CHECK", "ITEM", "PRICE")
         VALUES (1, to_date('2014-08-15','yyyy-mm-dd'), 4, 3, 2, 1004, 'Y', 5)
    INTO Table1 ("UNIT", "DOB", "MODEID", "PERIODID", "REVID", "CHECK", "ITEM", "PRICE")
         VALUES (1, to_date('2014-08-16','yyyy-mm-dd'), 2, 1, 2, 2002, 'O', 8)
    INTO Table1 ("UNIT", "DOB", "MODEID", "PERIODID", "REVID", "CHECK", "ITEM", "PRICE")
         VALUES (1, to_date('2014-08-16','yyyy-mm-dd'), 2, 1, 2, 2002, 'P', 41)
    INTO Table1 ("UNIT", "DOB", "MODEID", "PERIODID", "REVID", "CHECK", "ITEM", "PRICE")
         VALUES (1, to_date('2014-08-16','yyyy-mm-dd'), 2, 1, 2, 2002, 'H', 5)
    INTO Table1 ("UNIT", "DOB", "MODEID", "PERIODID", "REVID", "CHECK", "ITEM", "PRICE")
         VALUES (1, to_date('2014-08-16','yyyy-mm-dd'), 3, 2, 1, 2003, 'F', 52)
    INTO Table1 ("UNIT", "DOB", "MODEID", "PERIODID", "REVID", "CHECK", "ITEM", "PRICE")
         VALUES (1, to_date('2014-08-16','yyyy-mm-dd'), 3, 2, 1, 2003, 'R', 5)
    INTO Table1 ("UNIT", "DOB", "MODEID", "PERIODID", "REVID", "CHECK", "ITEM", "PRICE")
         VALUES (1, to_date('2014-08-16','yyyy-mm-dd'), 1, 2, 3, 2004, 'Y', 4)
    INTO Table1 ("UNIT", "DOB", "MODEID", "PERIODID", "REVID", "CHECK", "ITEM", "PRICE")
         VALUES (1, to_date('2014-08-16','yyyy-mm-dd'), 2, 3, 1, 2006, 'G', 8)
    INTO Table1 ("UNIT", "DOB", "MODEID", "PERIODID", "REVID", "CHECK", "ITEM", "PRICE")
         VALUES (1, to_date('2014-08-16','yyyy-mm-dd'), 2, 3, 1, 2006, 'H', 8)
    INTO Table1 ("UNIT", "DOB", "MODEID", "PERIODID", "REVID", "CHECK", "ITEM", "PRICE")
         VALUES (1, to_date('2014-08-16','yyyy-mm-dd'), 2, 3, 1, 2006, 'J', 2)
    INTO Table1 ("UNIT", "DOB", "MODEID", "PERIODID", "REVID", "CHECK", "ITEM", "PRICE")
         VALUES (1, to_date('2014-08-16','yyyy-mm-dd'), 2, 3, 1, 2006, 'K', 5)
    INTO Table1 ("UNIT", "DOB", "MODEID", "PERIODID", "REVID", "CHECK", "ITEM", "PRICE")
         VALUES (1, to_date('2014-08-17','yyyy-mm-dd'), 5, 4, 3, 2015, 'P', 31)
    INTO Table1 ("UNIT", "DOB", "MODEID", "PERIODID", "REVID", "CHECK", "ITEM", "PRICE")
         VALUES (1, to_date('2014-08-17','yyyy-mm-dd'), 5, 4, 3, 2015, 'W', 4)
    INTO Table1 ("UNIT", "DOB", "MODEID", "PERIODID", "REVID", "CHECK", "ITEM", "PRICE")
         VALUES (1, to_date('2014-08-17','yyyy-mm-dd'), 5, 3, 2, 2016, 'R', 5)
    INTO Table1 ("UNIT", "DOB", "MODEID", "PERIODID", "REVID", "CHECK", "ITEM", "PRICE")
         VALUES (1, to_date('2014-08-17','yyyy-mm-dd'), 5, 3, 2, 2016, 'D', 7)
    INTO Table1 ("UNIT", "DOB", "MODEID", "PERIODID", "REVID", "CHECK", "ITEM", "PRICE")
         VALUES (1, to_date('2014-08-17','yyyy-mm-dd'), 6, 3, 2, 2017, 'Q', 8)
    INTO Table1 ("UNIT", "DOB", "MODEID", "PERIODID", "REVID", "CHECK", "ITEM", "PRICE")
         VALUES (1, to_date('2014-08-17','yyyy-mm-dd'), 6, 3, 2, 2017, 'T', 2)
    INTO Table1 ("UNIT", "DOB", "MODEID", "PERIODID", "REVID", "CHECK", "ITEM", "PRICE")
         VALUES (1, to_date('2014-08-17','yyyy-mm-dd'), 6, 3, 2, 2017, 'U', 1)
    INTO Table1 ("UNIT", "DOB", "MODEID", "PERIODID", "REVID", "CHECK", "ITEM", "PRICE")
         VALUES (1, to_date('2014-08-17','yyyy-mm-dd'), 6, 3, 2, 2017, 'N', 4)
    INTO Table1 ("UNIT", "DOB", "MODEID", "PERIODID", "REVID", "CHECK", "ITEM", "PRICE")
         VALUES (1, to_date('2014-08-18','yyyy-mm-dd'), 2, 2, 1, 2052, 'W', 5)
    INTO Table1 ("UNIT", "DOB", "MODEID", "PERIODID", "REVID", "CHECK", "ITEM", "PRICE")
         VALUES (1, to_date('2014-08-18','yyyy-mm-dd'), 2, 2, 1, 2052, 'E', 2)
    INTO Table1 ("UNIT", "DOB", "MODEID", "PERIODID", "REVID", "CHECK", "ITEM", "PRICE")
         VALUES (1, to_date('2014-08-18','yyyy-mm-dd'), 2, 2, 1, 2052, 'D', 4)
    INTO Table1 ("UNIT", "DOB", "MODEID", "PERIODID", "REVID", "CHECK", "ITEM", "PRICE")
         VALUES (1, to_date('2014-08-18','yyyy-mm-dd'), 2, 2, 2, 2053, 'F', 5)
    INTO Table1 ("UNIT", "DOB", "MODEID", "PERIODID", "REVID", "CHECK", "ITEM", "PRICE")
         VALUES (1, to_date('2014-08-18','yyyy-mm-dd'), 2, 2, 2, 2053, 'R', 8)
    INTO Table1 ("UNIT", "DOB", "MODEID", "PERIODID", "REVID", "CHECK", "ITEM", "PRICE")
         VALUES (1, to_date('2014-08-18','yyyy-mm-dd'), 2, 2, 2, 2053, 'G', 9)
    INTO Table1 ("UNIT", "DOB", "MODEID", "PERIODID", "REVID", "CHECK", "ITEM", "PRICE")
         VALUES (1, to_date('2014-08-18','yyyy-mm-dd'), 2, 2, 2, 2053, 'H', 1)
SELECT * FROM dual
/

Open in new window

This query:
/* NB: "CHECK" is a reserved word & NOT a good column name */

select
      unit
    , season(DOB) as season
    , to_char(dob,'yyyy-mm-dd') as dob
    , modeid
    , periodid
    , revid
    , count("CHECK") as count_check
    , count(distinct "CHECK") as count_distinctcheck
    , SUM(price) as price
from table1
group by
      unit
    , season(DOB)
    , to_char(dob,'yyyy-mm-dd')
    , modeid
    , periodid
    , revid
;

Open in new window

produces the following result, I have included 2 count checks columns for comparison purposes. In this sample of data there is only one distinct "check" (which by the way I spell these cheque) per day
| UNIT | SEASON |        DOB | MODEID | PERIODID | REVID | COUNT_CHECK | COUNT_DISTINCTCHECK | PRICE |
|------|--------|------------|--------|----------|-------|-------------|---------------------|-------|
|    1 |      6 | 2014-08-16 |      2 |        1 |     2 |           3 |                   1 |    54 |
|    1 |      6 | 2014-08-18 |      2 |        2 |     2 |           4 |                   1 |    23 |
|    1 |      6 | 2014-08-15 |      1 |        2 |     3 |           3 |                   1 |    25 |
|    1 |      6 | 2014-08-16 |      1 |        2 |     3 |           1 |                   1 |     4 |
|    1 |      6 | 2014-08-15 |      2 |        1 |     2 |           4 |                   1 |    25 |
|    1 |      6 | 2014-08-17 |      5 |        4 |     3 |           2 |                   1 |    35 |
|    1 |      6 | 2014-08-17 |      6 |        3 |     2 |           4 |                   1 |    15 |
|    1 |      6 | 2014-08-18 |      2 |        2 |     1 |           3 |                   1 |    11 |
|    1 |      6 | 2014-08-16 |      2 |        3 |     1 |           4 |                   1 |    23 |
|    1 |      6 | 2014-08-15 |      4 |        3 |     2 |           3 |                   1 |    17 |
|    1 |      6 | 2014-08-17 |      5 |        3 |     2 |           2 |                   1 |    12 |
|    1 |      6 | 2014-08-16 |      3 |        2 |     1 |           2 |                   1 |    57 |

Open in new window

And removing the date column in the group by:
select
      unit
    , season(DOB) as season
    , count(distinct trunc(dob)) as count_of_days
    , modeid
    , periodid
    , revid
    , count("CHECK") as count_check
    , count(distinct "CHECK") as count_distinctcheck
    , SUM(price) as price
from table1
group by
      unit
    , season(DOB)
    , modeid
    , periodid
    , revid
;

Open in new window

produces this result:
| UNIT | SEASON | COUNT_OF_DAYS | MODEID | PERIODID | REVID | COUNT_CHECK | COUNT_DISTINCTCHECK | PRICE |
|------|--------|---------------|--------|----------|-------|-------------|---------------------|-------|
|    1 |      6 |             2 |      1 |        2 |     3 |           4 |                   2 |    29 |
|    1 |      6 |             2 |      2 |        1 |     2 |           7 |                   2 |    79 |
|    1 |      6 |             1 |      2 |        2 |     1 |           3 |                   1 |    11 |
|    1 |      6 |             1 |      2 |        2 |     2 |           4 |                   1 |    23 |
|    1 |      6 |             1 |      2 |        3 |     1 |           4 |                   1 |    23 |
|    1 |      6 |             1 |      3 |        2 |     1 |           2 |                   1 |    57 |
|    1 |      6 |             1 |      4 |        3 |     2 |           3 |                   1 |    17 |
|    1 |      6 |             1 |      5 |        3 |     2 |           2 |                   1 |    12 |
|    1 |      6 |             1 |      5 |        4 |     3 |           2 |                   1 |    35 |
|    1 |      6 |             1 |      6 |        3 |     2 |           4 |                   1 |    15 |

Open in new window

see it working at http://sqlfiddle.com/#!4/2819b4/2
0
 

Author Comment

by:NiceMan331
ID: 41026127
ok
first of all you are correct , "check" must be "cheque"

now , if we look to the raw data you insert
for example :
day 2015-08-16 , cheque # 2002 , ( there is 3 transactions for that cheque with total amount of 54)
in the result , cheque_count must be 1
so , we will change the name of : count_cheque to be item_count = 3
and counT-distinctcheck = cheque_count = 1

now in the last result you present it
actually i calculate some only , the calculation is correct , only one thing
"count_of_days" , as per your query if we consider each "MODEID | PERIODID | REVID" one day , your result is correct , but i want also to add overall count of days
i means in your sample data , you have inserted data for 4 days , ( from 15 till 18 )
so , i need  to add count_of_days = 4 beside each modeid - periodid - revid
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 41026417
>>"but i want also to add overall count of days"
fine

but not sure how you intend to do that because that is not how calculations by GROUP BY work, and in your example those 4 days would not be relevant for each output row,
| UNIT | SEASON | COUNT_OF_DAYS | MODEID | PERIODID | REVID | COUNT_CHECK | COUNT_DISTINCTCHECK | PRICE |
|------|--------|---------------|--------|----------|-------|-------------|---------------------|-------|
|    1 |      6 |             2 |      1 |        2 |     3 |           4 |                   2 |    29 |

Open in new window

below are the source data rows relevant to that one row of the grouped output
select *
from table1
where UNIT=1
and MODEID=1
and PERIODID=2
and REVID=3


| UNIT |                      DOB | MODEID | PERIODID | REVID | CHECK | ITEM | PRICE |
|------|--------------------------|--------|----------|-------|-------|------|-------|
|    1 | August, 15 2014 00:00:00 |      1 |        2 |     3 |  1002 |    A |    10 |
|    1 | August, 15 2014 00:00:00 |      1 |        2 |     3 |  1002 |    B |     8 |
|    1 | August, 15 2014 00:00:00 |      1 |        2 |     3 |  1002 |    C |     7 |
|    1 | August, 16 2014 00:00:00 |      1 |        2 |     3 |  2004 |    Y |     4 |

Open in new window

so you see there are only 2 days Aug 15 & Aug 16 that are available for calculation of the grouped output.

How do you propose I invent a way to make 4 days the result?
0
 

Author Comment

by:NiceMan331
ID: 41026437
How do you propose I invent a way to make 4 days the result?
it will be distinct count of days per unit , we will keep the 2 days which will help also later on , but if you select how many days  unit # 1 has data in season # 6 ,
in other word :
select *
from table1
where UNIT=1
and MODEID=1
and PERIODID=2
and REVID=3

we have 2 cheques only , within 4 actual working days of unit # 1
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 41027628
>>"we have 2 cheques only , "

Yes, 2 distinct cheques (1002 & 2004)

>>"within 4 actual working days of unit # 1"

I have no idea what you mean by that clause.
There are 2 distinct days (Aug 15 & 16)
& There are 2 elapsed days

The only 4 days I can see are the 4 rows each with a date, but 3 of those rows repeat the same date.
0
 

Author Comment

by:NiceMan331
ID: 41028004
i mean if we sum distinct days over unit & season , we will find unit # 1 in season # 6 has transactions in 4 days , 15-16-17 & 18
regardless of the specific mode - period or rev
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 200 total points
ID: 41028015
OK, yes, if you use group by rollup -or- group by cube you will get 4 distinct days for the whole of Unit #1

If you are going to use either of these group by rollup -or- group by cube you may also want to include GROUPING_ID()
select
      grouping_id(
                unit
              , season(DOB)
              , modeid
              , periodid
              , revid
                ) gid
    , unit
    , season(DOB) as season
    , count(distinct trunc(dob)) as count_of_days
    , modeid
    , periodid
    , revid
    , count("CHECK") as count_check
    , count(distinct "CHECK") as count_distinctcheck
    , SUM(price) as price
from table1
group by rollup (
      unit
    , season(DOB)
    , modeid
    , periodid
    , revid
  )
;

Open in new window

| GID |   UNIT | SEASON | COUNT_OF_DAYS | MODEID | PERIODID |  REVID | COUNT_CHECK | COUNT_DISTINCTCHECK | PRICE |
|-----|--------|--------|---------------|--------|----------|--------|-------------|---------------------|-------|
|   0 |      1 |      6 |             2 |      1 |        2 |      3 |           4 |                   2 |    29 |
|   0 |      1 |      6 |             2 |      2 |        1 |      2 |           7 |                   2 |    79 |
|   0 |      1 |      6 |             1 |      2 |        2 |      1 |           3 |                   1 |    11 |
|   0 |      1 |      6 |             1 |      2 |        2 |      2 |           4 |                   1 |    23 |
|   0 |      1 |      6 |             1 |      2 |        3 |      1 |           4 |                   1 |    23 |
|   0 |      1 |      6 |             1 |      3 |        2 |      1 |           2 |                   1 |    57 |
|   0 |      1 |      6 |             1 |      4 |        3 |      2 |           3 |                   1 |    17 |
|   0 |      1 |      6 |             1 |      5 |        3 |      2 |           2 |                   1 |    12 |
|   0 |      1 |      6 |             1 |      5 |        4 |      3 |           2 |                   1 |    35 |
|   0 |      1 |      6 |             1 |      6 |        3 |      2 |           4 |                   1 |    15 |
|   1 |      1 |      6 |             2 |      1 |        2 | (null) |           4 |                   2 |    29 |
|   1 |      1 |      6 |             2 |      2 |        1 | (null) |           7 |                   2 |    79 |
|   1 |      1 |      6 |             1 |      2 |        2 | (null) |           7 |                   2 |    34 |
|   1 |      1 |      6 |             1 |      2 |        3 | (null) |           4 |                   1 |    23 |
|   1 |      1 |      6 |             1 |      3 |        2 | (null) |           2 |                   1 |    57 |
|   1 |      1 |      6 |             1 |      4 |        3 | (null) |           3 |                   1 |    17 |
|   1 |      1 |      6 |             1 |      5 |        3 | (null) |           2 |                   1 |    12 |
|   1 |      1 |      6 |             1 |      5 |        4 | (null) |           2 |                   1 |    35 |
|   1 |      1 |      6 |             1 |      6 |        3 | (null) |           4 |                   1 |    15 |
|   3 |      1 |      6 |             2 |      1 |   (null) | (null) |           4 |                   2 |    29 |
|   3 |      1 |      6 |             3 |      2 |   (null) | (null) |          18 |                   5 |   136 |
|   3 |      1 |      6 |             1 |      3 |   (null) | (null) |           2 |                   1 |    57 |
|   3 |      1 |      6 |             1 |      4 |   (null) | (null) |           3 |                   1 |    17 |
|   3 |      1 |      6 |             1 |      5 |   (null) | (null) |           4 |                   2 |    47 |
|   3 |      1 |      6 |             1 |      6 |   (null) | (null) |           4 |                   1 |    15 |
|   7 |      1 |      6 |             4 | (null) |   (null) | (null) |          35 |                  12 |   301 |
|  15 |      1 | (null) |             4 | (null) |   (null) | (null) |          35 |                  12 |   301 |
|  31 | (null) | (null) |             4 | (null) |   (null) | (null) |          35 |                  12 |   301 |

Open in new window

see
http://sqlfiddle.com/#!4/2819b4/9
0
 

Author Comment

by:NiceMan331
ID: 41028063
yes , i think it is correct
now as i have a lot of other tasks
may i accept this solution then if i have any question about it i will open new topic
or , keep it open till next week till i try it ?
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 41028101
You can close it if you wish to, or you may leave it open until tested if you need to do that.

But you should not ask new questions at the end of this one.

You should also consider all contributions - did they help answer the question(s)?
0
 

Author Comment

by:NiceMan331
ID: 41028107
ok , so i will keep till next week
thanx alot for every all
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video shows how to recover a database from a user managed backup

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now