Results 1 to 3 of 3

Thread: Group By

  1. #1
    Join Date
    Nov 2011
    Posts
    6

    Unanswered: Group By

    The Below query get me the results I need:

    SELECT invoice,
    sum(CASE WHEN product like 'FREIGHT' THEN val ELSE 0 END) AS DeliveryCharge
    FROM scheme.opsahistm GROUP BY invoice

    But I need to tie this results set into another query, when I add in a join to this query to get more data it fails:

    SELECT order_no, invoice,
    sum(CASE WHEN product like 'FREIGHT' THEN val ELSE 0 END) AS DeliveryCharge
    FROM scheme.opsahistm join scheme.opheadm on scheme.opsahistm.invoice like scheme.opheadm.invoice_no
    GROUP BY invoice

    The error message is pointing to the Group By

    Will the group by keyword not work accross a join?

    Can anyone help with this?

  2. #2
    Join Date
    Sep 2011
    Location
    Greenville, SC USA
    Posts
    34
    Let me know the results you get with the following:

    Code:
    SELECT ord.order_no ,inv.invoice ,inv.DeliveryCharge
    FROM
      scheme.opheadm ord
     ,(SELECT invoice ,SUM(CASE WHEN product LIKE 'FREIGHT' THEN val ELSE 0 END) AS DeliveryCharge
       FROM scheme.opsahistm GROUP BY invoice
      ) inv
    WHERE
      inv.invoice = ord.invoice_no

  3. #3
    Join Date
    Nov 2011
    Posts
    6

    Thanks

    Thanks, that got what i needed

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •