2 Replies - 196 Views - Last Post: 15 July 2014 - 05:54 AM Rate Topic: -----

#1 wuu  Icon User is offline

  • D.I.C Head

Reputation: 3
  • View blog
  • Posts: 85
  • Joined: 16-March 09

How to retrive single record in combination with GROUP BY

Posted 15 July 2014 - 02:52 AM

Hi all!

I have interesting problem. I've got products that I put into the pallet. Pallet has it own serial number, product ID and quantity number. I'm using PostgreSQL for my database.

For this I use two tables

[pallets]
id_pallet
id_product
quantity

[products]
product_bar
id_pallet
product_timestamp
id_user

I use this code to retrieve data that I need:
SELECT products.id_pallets, pallets.id_produkt, CAST(count(*) AS integer) AS real_quantity, pallets.quantity FROM products 
JOIN pallets
ON pallets.id_pallets = products.id_pallets
GROUP BY products.id_pallets, pallets.id_produkt, pallets.quantity
HAVING count(*) < pallets.quantity



The result of above query looks like this:
id_pallet, id_product, real_quantity, quantity



What I want to do is to add product_timestamp of last product that was inserted into the pallet to the result.
How do I do that since all data that I retrive must appear in GROUP BY sentence, but I want to add to this result not the GROUP OF data but only one data (scalar)?

Any tips?

Is This A Good Question/Topic? 0
  • +

Replies To: How to retrive single record in combination with GROUP BY

#2 baavgai  Icon User is online

  • Dreaming Coder
  • member icon

Reputation: 5821
  • View blog
  • Posts: 12,674
  • Joined: 16-October 07

Re: How to retrive single record in combination with GROUP BY

Posted 15 July 2014 - 04:06 AM

I'm a rather confused by this. Presumably:
pallets ( why plural, always a bad idea )
    id_pallet (PK) ( why do you keep using id_pallets in your query?)
    id_product (PK)
    quantity

products
    ( um, where is the PK?  an id_product? )
    product_bar ( what is this? )
    id_pallet ( huh? if this is here, what's it doing in pallets? )
    product_timestamp
    id_user ( huh? )



Now, product timestamp, what? The timestamp belongs to the product.

Let's say, for the sake of argument, I had a warehouse of items where I wanted to put products on pallets and also return the age of that inventory and who I bought it from. My tables would look more like.

product
    product_id (PK)
    name
    quantity_on_hand
    ...

pallet
    pallet_id (PK)
    product_id (FK)
    vendor_id(FK)
    checkin_timestamp
    quantity



I'd first group the pallets:
select pallet_id, product_id, max(checkin_timestamp) as last_checkin, sum(quantity) as quantity
    from pallet
    group by pallet_id, product_id



I'd then use that as a sub query in a product select:
select a.product_id, a.name, a.quantity_on_hand
        b.pallet_id, b.last_checkin, b.real_quantity
    from product a
        left outer join (
            select pallet_id, product_id, max(checkin_timestamp) as last_checkin, sum(quantity) as real_quantity
                from pallet
                group by pallet_id, product_id
        ) b
            on a.product_id=b.product_id



Hope this helps.
Was This Post Helpful? 1
  • +
  • -

#3 wuu  Icon User is offline

  • D.I.C Head

Reputation: 3
  • View blog
  • Posts: 85
  • Joined: 16-March 09

Re: How to retrive single record in combination with GROUP BY

Posted 15 July 2014 - 05:54 AM

View Postbaavgai, on 15 July 2014 - 05:06 AM, said:

I'm a rather confused by this. Presumably:
pallets ( why plural, always a bad idea )
    id_pallet (PK) ( why do you keep using id_pallets in your query?)
    id_product (PK)
    quantity

products
    ( um, where is the PK?  an id_product? )
    product_bar ( what is this? )
    id_pallet ( huh? if this is here, what's it doing in pallets? )
    product_timestamp
    id_user ( huh? )



Now, product timestamp, what? The timestamp belongs to the product.

Let's say, for the sake of argument, I had a warehouse of items where I wanted to put products on pallets and also return the age of that inventory and who I bought it from. My tables would look more like.

product
    product_id (PK)
    name
    quantity_on_hand
    ...

pallet
    pallet_id (PK)
    product_id (FK)
    vendor_id(FK)
    checkin_timestamp
    quantity



I'd first group the pallets:
select pallet_id, product_id, max(checkin_timestamp) as last_checkin, sum(quantity) as quantity
    from pallet
    group by pallet_id, product_id



I'd then use that as a sub query in a product select:
select a.product_id, a.name, a.quantity_on_hand
        b.pallet_id, b.last_checkin, b.real_quantity
    from product a
        left outer join (
            select pallet_id, product_id, max(checkin_timestamp) as last_checkin, sum(quantity) as real_quantity
                from pallet
                group by pallet_id, product_id
        ) b
            on a.product_id=b.product_id



Hope this helps.



Thank you for your answer you've been really helpful.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1