2

I'm having a problem grouping a field in my query. Here is an example of what I'm talking about:

Example:

AIR_DT             DOL_GAP_TIME         MATL_SIZE                     
15-JAN-15          8:00 AM              30
15-JAN-15          8:00 AM              25
15-JAN-15          9:00 AM              5
15-JAN-15          9:00 AM              10
15-JAN-15          9:00 AM              5
15-JAN-15          9:00 AM              20

Those with same time should be grouped as one, summing up their matl_size

Expected output:

AIR_DT             DOL_GAP_TIME         MATL_SIZE                     
15-JAN-15          8:00 AM              55
15-JAN-15          9:00 AM              40

Here is my SQL:

SELECT 
      a.air_dt, 
      TRIM(SUBSTR(TO_CHAR (a.dol_pref_start_time, 'mm/dd/yyyy hh:mi:ss AM'),12,2))
      || ':00 '
      || TRIM(SUBSTR (TO_CHAR (a.dol_pref_start_time, 'mm/dd/yyyy hh:mi:ss    AM'),
21, 2)) dol_gap_time, e.matl_size
FROM order_implem_dtl_broadcast a, matl_mstr b, matl_size_mstr e 
WHERE a.matl_id = b.matl_id 
AND b.matl_size_id = e.matl_size_id  
AND a.air_dt LIKE '%15-JAN-15%'
GROUP BY a.air_dt, a.dol_pref_start_time, e.matl_size
ORDER BY a.air_dt, a.dol_pref_start_time;

Thank you for helping in advance!

Improve this question

2 Answers 2

Reset to default
2

Based on your sample data, this should do what you want:

select AIR_DT, DOL_GAP_TIME, sum(MATL_SIZE)
from table t
group by AIR_DT, DOL_GAP_TIME;

However, I have no idea what this has to do with the query in the question.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thank you for the swift respone Gordon Linoff, the query includes 3 tables. I tried mixing up your query to my query but unfortunately I still get the same result. I'm thinking of because the query is grouping it by the a.dol_pref_start_time value, I want it to group it by the string aggregated value (dol_start_time).
1

OK let's start with your initial query:

SELECT 
  a.air_dt, 
  TRIM(SUBSTR(TO_CHAR (a.dol_pref_start_time, 'mm/dd/yyyy hh:mi:ss AM'),12,2))
  || ':00 '
  || TRIM(SUBSTR (TO_CHAR (a.dol_pref_start_time, 'mm/dd/yyyy hh:mi:ss    AM'), 21, 2)) dol_gap_time, e.matl_size
  FROM order_implem_dtl_broadcast a, matl_mstr b, matl_size_mstr e 
 WHERE a.matl_id = b.matl_id 
   AND b.matl_size_id = e.matl_size_id  
   AND a.air_dt LIKE '%15-JAN-15%'
 GROUP BY a.air_dt, a.dol_pref_start_time, e.matl_size
 ORDER BY a.air_dt, a.dol_pref_start_time;

I see a couple of problems. One, you're grouping by e.matl_size even though that is the column you want to SUM(). You don't want it in the GROUP BY. Second, your manner of getting the time from dol_pref_start_time is really odd. It looks like you want to round down to the hour, then just get the hour plus whether it is AM or PM. So this:

TRIM(SUBSTR(TO_CHAR (a.dol_pref_start_time, 'mm/dd/yyyy hh:mi:ss AM'),12,2))
  || ':00 '
  || TRIM(SUBSTR (TO_CHAR (a.dol_pref_start_time, 'mm/dd/yyyy hh:mi:ss    AM'), 21, 2)) dol_gap_time

can simply be this:

TO_CHAR(TRUNC(a.dol_pref_start_time, 'HH'), 'HH:MI AM') AS dol_gap_time

Third, are your dates stored as dates? If so, why are you doing this?

AND a.air_dt LIKE '%15-JAN-15%'

It would be far better to do this:

AND TRUNC(a.air_dt) = date'2015-01-15'

or, if you have an index on a.air_dt, this:

AND a.air_dt >= date'2015-01-15'
AND a.air_dt < date'2015-01-16'

Putting this all together, we get something like this (note that I've also converted your joins to ANSI SQL joins):

SELECT TRUNC(a.air_dt) AS air_dt
     , TO_CHAR(TRUNC(a.dol_pref_start_time, 'HH'), 'HH:MI AM') AS dol_gap_time
     , SUM(e.matl_size) AS matl_size
  FROM order_implem_dtl_broadcast a INNER JOIN matl_mstr b
    ON a.matl_id = b.matl_id
 INNER JOIN matl_size_mstr e 
    ON b.matl_size_id = e.matl_size_id
 WHERE a.air_dt >= date'2015-01-15'
   AND a.air_dt < date'2015-01-16'
 GROUP BY TRUNC(a.air_dt), TO_CHAR(TRUNC(a.dol_pref_start_time, 'HH'), 'HH:MI AM')
 ORDER BY air_dt, TO_DATE(dol_gap_time, 'HH:MI AM'); -- using aliases in the ORDER BY, converting dol_gap_time to DATE for sorting
Improve this answer

5 Comments

Thank you David Faber. I revised that code and removed the matl_size in the group by clause. Yes thats correct I want to round down to the hour, i'll try all of your suggestions. This is really helpful.
Hi again David. I tried your query and it worked on summing up the matl_size and grouping it by dol_gap_time. My only problem now is how can i sort the dol_gap_time as time? I need it to treat dol_gap_time as time, currently i think it is being sorted as string. I tried using the to_date function in dol_gap_time in the order by clause, unfortunately its not working for me.
Disregard the sorting of dol_gap_time @David Faber. I tried using the the to_date function in the other by clause and resolved my time sorting issue.
Once again thank you David and to those who helped. I'll mark this one as answered. More powers to you guys!
@rgstamayo, thanks for pointing out the error in the ORDER BY clause. I am updating my answer to use TO_DATE(dol_gap_time, 'HH:MI AM')

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.