Getting sales quantity per country with a query, an idea anyone?

#1

Hi,

For the purpose of some charts on a dashboard for our ERP i would like to get the list of countries and the sales quantity for each of them. I am pretty sure it is somehow possible to get that directly from a more or less complex query but i failed to find it; Does any have an idea?

The entity (we call it OrderData) contains fields and other entities related to an order, fields we are interrested in are  buyerCountry and orderPrice.

Making a list of all possible country then a list of concerned countries (: with orders) and finally a list of the order quantity per country is too slow and inefficient.

Thanks.

#2

Possibly you may be able to retrieve that information using a GROUP BY query.

ObjectDB Support
#3

Thank you very much! it was just there, shame on me.

 

#4

The new question is not related to the subject of this thread.

Please follow the posting instructions and use a separate thread for every subject.

ObjectDB Support
#5

I can now reply my own question in case anyone need to achieve the same.

This call will retrieve a list of 3 dimensional Object array containing respectively countries, quantity of orders and sum of orders, per country, only including valid orders (ord.getPaidTimeD()) and for a given month back in time. The resulting Object list contains the 3 SELECT items, here String, Integer and Double.

Its very nice to get all that in one single query, this call is used for a pie chart.


public static List<Object[]>  getOrdersPerCountry4Month(int monthBack) {

        int i=0; //NOTE : monthBack is a negative integer     
        Calendar now = new GregorianCalendar(TimeZone.getTimeZone("UTC+01:00")).getInstance();
        Calendar to = (GregorianCalendar) now.clone();
        to.add(Calendar.MONTH, monthBack + 1);
        to.set(to.get(Calendar.YEAR), to.get(Calendar.MONTH), 1);
        if (to.after(now)) {to = now;}
        Calendar from = (GregorianCalendar) now.clone();
        from.add(Calendar.MONTH, monthBack);
        from.set(from.get(Calendar.YEAR), from.get(Calendar.MONTH), 1);

        Query q2 = em.createQuery("SELECT "
                                + "ord.getCountry(),"                               
                                + "COUNT(ord),"
                                + "SUM(ord.getAmountPaid()) "
                                + "FROM OrderData ord "
                                + "WHERE ord.getPaidTimeD() "
                                + "BETWEEN :fromD AND :toD "
                                + "GROUP BY ord.getCountry() ");
        q2.setParameter("fromD", from.getTime());
        q2.setParameter("toD", to.getTime()); 
        List<Object[]> res = q2.getResultList();      
        for (Object[]  ord2 : res){
            System.out.printf("%s\t%d\t%.1f\n",ord2[0],ord2[1],ord2[2]);                                   
            i+=1;
        }        
        return res;

    }

Reply