0

I am trying to create a query that retrieves only the ten companies with the highest number of pickups over the six-month period, this means pickup occasions, and not the number of items picked up. I have done this

cv28
  • 51
  • 7
  • 1
    It will be useful to provide test data and expected results that we can use? an [SQLFiddle](http://sqlfiddle.com) would be nice. Good start with the table definitions. – Ryan Vincent Nov 10 '15 at 16:24
  • Hi, its meant to return two rows. One called company_name (with the name of the company) and the other called pickups (number of pickups the company has completed. – cv28 Nov 10 '15 at 16:25
  • Sample inserts and expected output (perhaps you could say "I want the top 2 pickups out of these 4 companies") would help us enormously to be able to help you. – Boneist Nov 10 '15 at 16:26
  • I dont get "this means pickup occasions, and not the number of items picked up." this part , what exactly do you want ? – ogres Nov 10 '15 at 16:26
  • please read [**How to ask**](http://stackoverflow.com/help/how-to-ask) And [**How to create a Minimal, Complete, and Verifiable example.**](http://stackoverflow.com/help/mcve) this is a great place to start http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/ – Juan Carlos Oropeza Nov 10 '15 at 16:29
  • I am looking to count how many pickups a company has done. Pickups are when item(s) are picked up by the pickup company. I am looking to find a way to display how many pickups the company has done, and not the number of items the company has picked up. As I said, trying to explain this the best I can, sorry. – cv28 Nov 10 '15 at 16:29
  • Hi, I do not have test data as the query does not work. – cv28 Nov 10 '15 at 16:38
  • 1
    you should probably look at the Dense_Rank function http://www.techonthenet.com/oracle/functions/dense_rank.php – JamieD77 Nov 10 '15 at 16:42
  • "I do not have test data as the query does not work" ... really? The query doesn't have to work in order for you to have some sample data in your tables and know what you want the output to look like! We need some data to work with plus what you're expecting to see as we aren't sat next to you, and we don't have your data or requirements in front of us to play with. We are dependent on the information you provide. If you don't provide us with any, it's extremely difficult to work out what you mean. – Boneist Nov 10 '15 at 16:57
  • @cv28 - 'test data' means examples of the data in the tables (customer, trip, manifest), preferably as insert statements to make it easier to recreate what you're working with (or a Fiddle, as suggested earlier). The output you expect from that data would also be useful. – Alex Poole Nov 10 '15 at 16:58
  • If I was you, first off, I'd work out a query to find out how many trips were made per company. I would expect it to look something like `select company_name, count(*) from .... group by company_name;` P.S. [Here's an example](http://stackoverflow.com/questions/33549110/more-efficient-way-to-find-employees-with-coverage-between-two-dates) of someone who included some test data and expected output with their question. Perhaps you could do similar (although insert statements to go along with your create table statements would be appreciated!)? – Boneist Nov 10 '15 at 16:59

2 Answers2

1
SELECT  * 
FROM    customer
JOIN (SELECT manifest.pickup_customer_ref reference,
             DENSE_RANK() OVER (PARTITION BY manifest.pickup_customer_ref ORDER BY COUNT(manifest.trip_id) DESC) rnk
      FROM   manifest 
      INNER JOIN trip ON manifest.trip_id = trip.trip_id
      WHERE trip.departure_date > TRUNC(SYSDATE) - interval '6' month
      GROUP BY manifest.pickup_customer_ref) cm ON customer.reference = cm.reference
WHERE cm.rnk < 11;

this uses dense_rank to determine the order or customers with the highest number of trips first

JamieD77
  • 13,796
  • 1
  • 17
  • 27
0

Hmm well i don't have Oracle so I can't test it 100%, but I believe your looking for something like the following:

Keep in mind that when you use group by, you have to narrow down to the same fields you group by in the select. Hope this helps at least give you an idea of what to look at.

select TOP 10 
    c.company_name,
    m.pickup_customer_ref,
    count(*) as 'count'
from customer c
inner join mainfest m on m.pickup_customer_ref = c.reference
inner join trip t on t.trip_id = m.trip_id
where t.departure_date < DATEADD(month, -6, GETDATE())
group by c.company_name, m.pickup_customer_ref
order by 'count', c.company_name, m.pickup_customer_ref desc
Mercyful
  • 107
  • 1
  • 7
  • Oracle doesn't support `TOP`, `DATEADD()` or `GETDATE()`; and column aliases can't be in single quotes... – Alex Poole Nov 10 '15 at 20:32
  • I knew I hated oracle for a reason! :-) Well there are easy replacements for those if they are looked up, but the gist is still the same. – Mercyful Nov 11 '15 at 01:35