0

Using Postgres 9.3, I have problem with a select over few tables. I have simcards stock and I would like to get something like this:

| network | supplier | total topups (EUR) | 1st topup qty|   
| three   | three    | 500                | 34           |  
| three   | core     | 300                | 4            |  
| o2      | o2       | 100                | 3            |  

| network | supplier | total topups (EUR) | 2nd topup qty|   
| three   | three    | 200                | 4            |  

| network | supplier | total topups (EUR) | 3rd topup qty|   
| three   | three    | 200                | 4            |  

my table schemas are:

  • simcard(id, iccid(unique), network_id, supplier_id)
  • topup(id, date, amount, simcard_id)
  • network(id, name)
  • supplier(id, name)

at the moment I am able to select all first topups, but I don't know how to display them in a format as I would like (network| supplied by| topups sum| 1st topup qty)
Then I have no idea how to select 2nd, 3rd topup for every simcard. I tried with limit 1 offset 1 or offset 2 but it didn't work.

select 
    distinct topup.iccid    
    , s.created_date
    , n.name as "network"
    , p.name as "supplier"
from
(
select 
    s.iccid as "iccid"
    , t.date

from 
    simcard_topup as t
    join simcard_simcard as s
        on t.iccid_id = s.id
--where 
--  CAST(t.date AS DATE) = date '2015-03-13' - integer '14'

group by
    s.iccid
    , t.date
order by
    t.date asc
) as topup
join simcard_simcard as s
    on topup.iccid = s.iccid
join simcard_network as n
    on s.network_id = n.id
join simcard_supplier as p
    on s.supplier_id = p.id
  • Please always provide your version of Postgres. – Erwin Brandstetter Mar 15 '15 at 2:43
1
SELECT st.topup_nr, n.name AS network, p.name AS supplier
     , st.topup_sum, st.topup_qty
FROM  (
   SELECT t.topup_nr, s.network_id, s.supplier_id
        , sum(t.amount) AS topup_sum
        , count(*) AS topup_qty
   FROM  (
      SELECT simcard_id, amount
           , row_number() OVER (PARTITION BY simcard_id, ORDER BY date) AS topup_nr
      FROM   simcard_topup
      ) t
   JOIN   simcard_simcard s ON s.id = t.simcard_id  -- or so I assume
   GROUP  BY 1,2,3
   ) st
JOIN   simcard_network  n ON n.id = st.network_id
JOIN   simcard_supplier p ON p.id = st.supplier_id
ORDER  BY st.topup_nr, st.topup_qty DESC NULLS LAST
                     , st.topup_sum DESC NULLS LAST;

Notes

  • The core feature is the window function row_number() in the inner subquery to number topups per simcard. This makes aggregating for 1st, 2nd etc. topup in the outer query simple.

  • For lack of information, assuming to join simcard_simcard to simcard_topup on s.id = t.simcard_id, though you also have on t.iccid_id = s.id in your code, which seems backwards ...

  • Don't use a basic type name like date as column name.

  • Why do you have CAST(t.date AS DATE) in your code? A date should be a date, so no need to cast it to date. Also, it seems we only need the date for ranking topups.

  • Counting per (network_id, supplier_id) and sorting the result by ... topup_qty DESC, topup_sum DESC, so the combination with the most topups comes first. NULLS LAST is just to be safe against NULL values.

  • wow... now I need to study it a lot to make sure I understand what it does. thank you very much specially for tips how to improve my coding. – gerpaick Mar 15 '15 at 12:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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