Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a problem with sql data aggregation problem.here is the sample sql.I am using oracle database.

select pm.city cityid, to_char(pm.sampledate, 'yyyy-MM') timepoint, count(distinct   me.id) mecount, count(distinct me.hotpointid) hpcount
from upme me, appmday pm
where pm.apid = me.id
          and pm.sampledate between to_date('2011-11-01', 'yyyy-MM-dd') and
          to_date('2012-1-31', 'yyyy-MM-dd')
group by pm.city, to_char(pm.sampledate, 'yyyy-MM')

above i get the count of my data.but i also i want to get the overall result like

select   to_char(pm.sampledate, 'yyyy-MM'), count(distinct me.id), count(distinct me.hotpointid)
from upme me, appmday pm
        where pm.apid = me.id
              and pm.sampledate between to_date('2011-11-01', 'yyyy-MM-dd') and
              to_date('2012-1-31', 'yyyy-MM-dd')
              group by to_char(pm.sampledate, 'yyyy-MM')

basically i want get some result from sql ,in the same sql i want to get the count from the previous result,they both show in the result in one sql. i did not want to union these two sql ,because each sql is very slow.

did someone have a idea? or it cannot be done by one sql without union?

thank you for the reply.

share|improve this question
1  
What database system, and which version?? SQL is just the Structured Query Language - a language used by many database systems - SQL is NOT a database product... stuff like this is very often vendor-specific - so we really need to know what database system you're using.... –  marc_s Feb 20 '12 at 8:45
    
thanks. we using oracle 10g. –  snow8261 Feb 20 '12 at 8:50
    
Should be oracle ( to_char, to_data, ... ) –  danihp Feb 20 '12 at 8:51
    
i found there is a dba forum.so i also post it there. –  snow8261 Feb 20 '12 at 10:50
    
Can you provide your table structure, some sample data and expected output? –  Alex Poole Feb 20 '12 at 12:03

2 Answers 2

up vote 4 down vote accepted

You can use analytic functions to achieve this:

select distinct pm.city cityid,
    to_char(trunc(pm.sampledate, 'MM'), 'YYYY-MM') as timepoint,
    count(distinct me.id)
        over (partition by pm.city, trunc(pm.sampledate, 'MM')) as mecount,
    count(distinct me.hotpointid)
        over (partition by pm.city, trunc(pm.sampledate, 'MM')) as hpcount,
    count(distinct me.id)
        over (partition by trunc(pm.sampledate, 'MM')) as totmecount,
    count(distinct me.hotpointid)
        over (partition by trunc(sampledate, 'MM')) as tothpcount
from upme me
join appmday pm on pm.apid = me.id
where pm.sampledate between to_date('2011-11-01', 'yyyy-MM-dd')
    and to_date('2012-1-31', 'yyyy-MM-dd');

Or instead of a distinct, use a subquery and a row_number() calculation to remove the duplicates:

select cityid, to_char(timepoint, 'YYYY-MM') as timepoint,
    mecount, hpcount, totmecount, tothpcount
from (
    select pm.city cityid,
        row_number() over (partition by pm.city, trunc(pm.sampledate, 'MM')
            order by pm.city, trunc(pm.sampledate, 'MM')) as rn,
        trunc(pm.sampledate, 'MM') as timepoint,
        count(distinct me.id)
            over (partition by pm.city, trunc(pm.sampledate, 'MM')) as mecount,
        count(distinct me.hotpointid)
            over (partition by pm.city, trunc(pm.sampledate, 'MM')) as hpcount,
        count(distinct me.id)
            over (partition by trunc(pm.sampledate, 'MM')) as totmecount,
        count(distinct me.hotpointid)
            over (partition by trunc(sampledate, 'MM')) as tothpcount
    from upme me
    join appmday pm on pm.apid = me.id
    where pm.sampledate between to_date('2011-11-01', 'yyyy-MM-dd')
        and to_date('2012-1-31', 'yyyy-MM-dd')
)
where rn = 1;

If your existing query is slow you may see a performance difference between the two options, otherwise it's a matter or preference.

(Also you may be aware of this, but if your sampledate has a time component, having your where clause end on '2012-1-31' means you'll only get data up to the start of that day, and not for example anything at 2012-01-31 01:00.)

share|improve this answer
    
the row_number() calculation is fast than the distinct one.thanks a lot. –  snow8261 Feb 20 '12 at 12:51

You would need to use analytic functions to get that result. I'm not completely sure that I understand exactly what results you're looking for, but something like this would appear to be what you're looking for.

select distinct
       pm.city cityid, 
       to_char(pm.sampledate, 'yyyy-MM') timepoint, 
       count(distinct   me.id) 
         over (partition by to_char(pm.sampledate, 'yyyy-mm'), pm.city) mecount_1, 
       count(distinct me.hotpointid) 
         over (partition by to_char(pm.sampledate, 'yyyy-mm'), pm.city) hpcount_1,
       count(distinct   me.id) 
         over (partition by to_char(pm.sampledate, 'yyyy-mm')) mecount_2, 
       count(distinct me.hotpointid) 
         over (partition by to_char(pm.sampledate, 'yyyy-mm')) hpcount_2
  from upme me,
       appmday pm
 where pm.apid = me.id
   and pm.sampledate between to_date('2011-11-01', 'yyyy-MM-dd') and
          to_date('2012-1-31', 'yyyy-MM-dd')
share|improve this answer
    
thanks a lot. but i found it takes more time than just union these two sql. –  snow8261 Feb 20 '12 at 11:10

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.