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

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.

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

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

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.

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;

    }