How to get min and max of group query in oracle>

Question

0.00/5 (No votes)

See more:

I have this query that sum the CT of each div_f,PROC_NO,SERIAL_NO.

I want to get the min and max of each proc_no and div_f.

for example

select sum(ct),div_f,PROC_NO,SERIAL_NO from Table1 group by div_f,PROC_NO,SERIAL_NO;

this return:

C++

sum(ct) div_f   proc_no  serial_no
559	1	1	1121
586	1	1	1122
568	1	1	1123
322	1	1	1124
223	1	1	16523
1180	1	1	16534
1375	1	1	16535
875	1	1	16536
202	1	1	16559
249	1	1	16560
139	1	1	16564
135	1	1	16565
134	1	1	16566
106	1	1	16567
125	1	1	16568
1297	1	1	17723
1291	1	1	17724
1248	1	1	17725
1199	1	1	17726
1080	1	1	17727
1233	1	1	17728
1281	1	1	17729
1150	1	1	17730
1308	1	1	17731
1110	1	1	17732
1434	1	1	17733
1176	1	1	17734
1996	1	1	17750
2003	1	1	17751
1895	1	1	17752
2170	1	1	17753
2252	1	1	17754
1972	1	1	17755
2087	1	1	17756
1700	1	1	17757
1777	1	1	17758
2027	1	1	17759
2419	1	1	17760
1928	1	1	17761
1850	1	1	17762
1296	1	1	17905
1188	1	1	17906
1220	1	1	17907
1048	1	1	17908
1319	1	1	17909
1330	1	1	17910
1366	1	1	17911
1436	1	1	17912
209	1	1	2424
182	1	1	2425
247	1	1	2426
184	1	1	2427
201	1	1	2428
178	1	1	2429
267	1	1	2430
225	1	1	2431
206	1	1	2432
167	1	1	2433
194	1	1	2434
256	1	1	2435
296	1	1	2437
160	1	1	2438
208	1	1	2439
168	1	1	2440
176	1	1	2441
196	1	1	2442
191	1	1	2443
187	1	1	2444
254	1	1	2445
197	1	1	2446
173	1	1	2447
282	1	1	2448
176	1	1	2449
324	1	1	2450
195	1	1	2451
269	1	1	2452
211	1	1	2453
206	1	1	2454
192	1	1	2455
235	1	1	2456
212	1	1	2457
215	1	1	2458
243	1	1	2459
279	1	1	2460
202	1	1	2461
101	1	1	25
516	1	1	2945
762	1	1	2966
371	1	1	2967
623	1	1	2968
716	1	1	2969
599	1	1	2970
694	1	1	2971
470	1	1	2972
539	1	1	2973
2928	1	1	3187
2545	1	1	3188
2353	1	1	3189
2609	1	1	3190
2367	1	1	3191
2532	1	1	3192
1199	1	1	3330
1199	1	1	3331
1275	1	1	3332
624	1	1	3662
957	1	1	3663
852	1	1	3664
506	1	1	3665
849	1	1	3666
953	1	1	3667
996	1	1	3668
941	1	1	3669
281	1	1	3670
968	1	1	3671
976	1	1	3672
1019	1	1	3673
946	1	1	3674
355	1	1	3675
3026	1	1	3975
2724	1	1	3976
2534	1	1	3977
2376	1	1	3978
1748	1	1	3979
1082	1	1	7631
1090	1	1	7632
1139	1	1	7633
1045	1	1	7634
1351	1	1	7635
1313	1	1	7636
1078	1	1	7637
1373	1	1	7638
1296	1	1	7639
2239	1	1	97
3395	1	1	98
1	4	1	17718
154	1	10	1038
301	1	10	1226
267	1	10	1227
271	1	10	1228
229	1	10	1229
272	1	10	1230
220	1	10	1231
228	1	10	1232
198	1	10	1233
231	1	10	1234
127	1	10	1391
126	1	10	1431
125	1	10	1433
139	1	10	1440
146	1	10	1509
139	1	10	1514
150	1	10	1518
228	1	10	1556
2537	1	10	17751
2512	1	10	17753
1274	1	10	17904
1256	1	10	17905
1408	1	10	17906
934	1	10	17907
1210	1	10	17908
884	1	10	17909
128	1	10	2327
117	1	10	2373
78	1	10	2391
99	1	10	2400
227	1	10	2423
216	1	10	2424
205	1	10	2425
187	1	10	2426
233	1	10	2427
203	1	10	2428
214	1	10	2429
228	1	10	2430
209	1	10	2431
280	1	10	2432
213	1	10	2433
207	1	10	2434
191	1	10	2435
228	1	10	2436
234	1	10	2437
221	1	10	2438
220	1	10	2439
217	1	10	2440
215	1	10	2441
249	1	10	2442
224	1	10	2443
247	1	10	2444
207	1	10	2445
218	1	10	2446
216	1	10	2447
214	1	10	2448
197	1	10	2449
257	1	10	2450
225	1	10	2451
238	1	10	2452
226	1	10	2453
238	1	10	2454
250	1	10	2455
224	1	10	2456
218	1	10	2457
239	1	10	2458
269	1	10	2459
260	1	10	2460
233	1	10	2461
630	1	10	3329
659	1	10	3330
692	1	10	3331
523	1	10	3973
91	1	10	4189
118	1	10	4198
116	1	10	4200
133	1	10	4201
106	1	10	4202
100	1	10	4203
574	1	10	7628
609	1	10	7629
713	1	10	7630
706	1	10	7631
688	1	10	7632
671	1	10	7633
645	1	10	7634
651	1	10	7635

I want to show only

min max div_f proc_no
101 3395 1 1
78 2537 1 10
1 1 4 1

is this possible in query only?
or another way?

What I have tried:

I have tried queries and loop it but it takes time to get what I want . and its too slow if its on web. so I'm trying if its possible to get it in one query only. Thanks

Posted 7-Jul-16 17:46pm

alvinjayson

Updated 7-Jul-16 19:59pm

OriginalGriff

Add a Solution

2 solutions

Solution 1

Try:

SQL

SELECT MIN(ct),MAX(ct), div_f,PROC_NO,SERIAL_NO FROM Table1 GROUP BY div_f,PROC_NO,SERIAL_NO;

Posted 7-Jul-16 19:25pm

OriginalGriff

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

alvinjayson · Accepted Answer · 2016-07-07T19:59:00

I already try that before. but not working .

but I already solved it . thanks :)

I used :

SELECT DIV_F AS Division,PROC_NO AS Process_Number, MIN(sumCT) as min_CT, MAX(sumCT) as max_CT , round(avg(sumCT),2) as AVERAGE,count(sumCT) as trial_QTY from (select sum(ct) as sumCT,div_f,PROC_NO,SIRIAL_NO from TL_HISTORY_CHECKSHEET where To_Char(To_Date(INPUT_DATE,'mm-dd-yyyy hh:mi:ss am'),'mm/yyyy') = '07/2016' group by div_f,PROC_NO,SIRIAL_NO) group by div_f,PROC_NO;