Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 53
  • Last Modified:

grouping data by days

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
NiceMan331
Asked:
NiceMan331
  • 22
  • 7
  • 6
  • +3
4 Solutions
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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
 
NiceMan331Author Commented:
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
 
Pratik MakwanaCommented:
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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
NiceMan331Author Commented:
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
 
awking00Commented:
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
 
MikeOM_DBACommented:
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
 
NiceMan331Author Commented:
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
 
MikeOM_DBACommented:
There is no period = '5' in your sample data,
0
 
NiceMan331Author Commented:
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
 
MikeOM_DBACommented:
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
 
NiceMan331Author Commented:
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
 
NiceMan331Author Commented:
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
 
MikeOM_DBACommented:
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
 
NiceMan331Author Commented:
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
 
MikeOM_DBACommented:
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
 
NiceMan331Author Commented:
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
 
NiceMan331Author Commented:
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
 
MikeOM_DBACommented:
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
 
NiceMan331Author Commented:
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
 
awking00Commented:
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
 
NiceMan331Author Commented:
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
 
NiceMan331Author Commented:
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
 
awking00Commented:
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
 
NiceMan331Author Commented:
Ok , good idea also , thanx
0
 
NiceMan331Author Commented:
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
 
awking00Commented:
Are you saying that there is only one unit per season? Exactly what does a unit represent?
0
 
NiceMan331Author Commented:
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
 
awking00Commented:
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
 
awking00Commented:
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
 
awking00Commented:
Forgot the attached file :-(
ExpandedTest.xlsx
0
 
NiceMan331Author Commented:
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
 
NiceMan331Author Commented:
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
 
PortletPaulCommented:
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
 
NiceMan331Author Commented:
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
 
PortletPaulCommented:
>>"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
 
NiceMan331Author Commented:
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
 
PortletPaulCommented:
>>"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
 
NiceMan331Author Commented:
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
 
PortletPaulCommented:
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
 
NiceMan331Author Commented:
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
 
PortletPaulCommented:
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
 
NiceMan331Author Commented:
ok , so i will keep till next week
thanx alot for every all
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 22
  • 7
  • 6
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now