• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • Grouping a Union

    March 19, 2008 Hey, Ted

    We have several sales history files–one for each year. When I need to combine data for more than one year, I have to use an SQL UNION. I am trying to group “unioned” data and can’t seem to get it right. Is it possible to group a dataset that is built by a union?

    –Tom

    This appears to be an easy problem to resolve, but appearances can be deceiving. If you’re not careful, you can get the wrong results when you UNION two or more tables, especially when you’re summarizing data.

    Divide the query into two steps: one to union the data, the other to group it. Assuming you have two files, one for the current year and one for the previous year, here’s one possibility.

    WITH Sales AS 
    (SELECT Customer, Item, QtyShipped, UnitPrice
      FROM CurrYear
    UNION ALL
     SELECT Customer, Item, QtyShipped, UnitPrice
      FROM PrevYear
    )
    SELECT Customer, Item, SUM(QtyShipped), 
           SUM(QtyShipped * UnitPrice)
      FROM Sales
     GROUP by Customer, Item
     ORDER BY Customer, Item;
    

    The WITH defines a common table expression, which creates a temporary table of the combined data. This temporary table is called SALES. Notice I used UNION ALL, not UNION, so duplicate rows would not be omitted. The last SELECT summarizes the combined data on customer and item number.

    Here’s another query that gives the same results. It summarizes each file, then summarizes the two summaries. I doubt this method is any better than the first one.

    WITH SalesSummary AS 
    (SELECT Customer, Item, SUM(QtyShipped) AS qty,
           SUM(QtyShipped * UnitPrice) AS sale  
      FROM CurrYear
     GROUP by Customer, Item
    UNION ALL
     SELECT Customer, Item, SUM(QtyShipped) AS qty,
           SUM(QtyShipped * UnitPrice) AS sale  
      FROM PrevYear
     GROUP by Customer, Item)
    SELECT Customer, Item, SUM(qty), SUM(sale) FROM SalesSummary
    GROUP by Customer, Item
    ORDER BY Customer, Item;
    

    I googled and found a similar question at http://joecelkothesqlapprentice.blogspot.com/. I based the following SQL query on Joe Celko’s answer.

    SELECT sale.Customer, sale.Item, 
           SUM(sale.QtyShipped), 
           SUM(sale.QtyShipped * sale.UnitPrice)
      FROM
       (SELECT Customer, Item, QtyShipped, UnitPrice
          FROM CurrYear
         UNION ALL
        SELECT Customer, Item, QtyShipped, UnitPrice
          FROM PrevYear) AS sale
     GROUP by Customer, Item
     ORDER BY Customer, Item;
    

    This query differs from the first example in that the union has been embedded in the FROM clause.

    BTW, in an ideal world, all sales history would be stored in one big file and transactions would be extracted by date. I find it more practical to keep separate files of history. It appears to me that many other people think the same.

    –Ted

    RELATED STORIES

    Weird SQL Union Results

    Time for a Common Table Expression, Part 2



                         Post this story to del.icio.us
                   Post this story to Digg
        Post this story to Slashdot

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags:

    Sponsored by
    ARCAD Software

    [Webinar Series] Demystifying DevOps on the IBM i

    Join us for this 3-Part Roundtable Webinar Series, where ARCAD experts will demystify the move to Git and an automated process with options that work for everyone!

    This Series will be an engaging discussion on key DevOps topics as Git, Builds, Automation and much more.

    Part 1: IBM i & Git – Developer Tools (Thursday, January 26th, at 12:00PM ET / 9:00AM PT)

    During this 1st session, we will discuss Developer Tools and all the ways to use Git including iProject, Merlin, VS Code, ARCAD’s Centralized option and more.

    Part 2: Branching & Building (Thursday, February 9th, at 12:00PM ET / 9:00AM PT)

    During this 2nd session, we will discuss Feature/Release and Branch Management and building the branches with tools like Bob and ARCAD.

    Part 3: DevOps – Automated Workflow (Thursday, February 23rd, at 12:00PM ET / 9:00AM PT)

    During this 3rd session, we will discuss what can be automated in the IBM i DevOps process starting from the build to other steps in your workflow.  We’ll discuss pipeline tools like Jenkins and the new automation features of Git packages.

    Register NOW

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Sponsored Links

    COMMON:  Join us at the annual 2008 conference, March 30 - April 3, in Nashville, Tennessee
    LANSA:  It's Time for 4 days of education at the LANSA User Conference, May 4 – 7, in Orlando
    MoshiMoshi:  An Interactive Experience for the System i Community. Coming March 30.

    IT Jungle Store Top Book Picks

    Easy Steps to Internet Programming for AS/400, iSeries, and System i: List Price, $49.95
    Getting Started with PHP for i5/OS: List Price, $59.95
    The System i RPG & RPG IV Tutorial and Lab Exercises: List Price, $59.95
    The System i Pocket RPG & RPG IV Guide: List Price, $69.95
    The iSeries Pocket Database Guide: List Price, $59.00
    The iSeries Pocket Developers' Guide: List Price, $59.00
    The iSeries Pocket SQL Guide: List Price, $59.00
    The iSeries Pocket Query Guide: List Price, $49.00
    The iSeries Pocket WebFacing Primer: List Price, $39.00
    Migrating to WebSphere Express for iSeries: List Price, $49.00
    iSeries Express Web Implementer's Guide: List Price, $59.00
    Getting Started with WebSphere Development Studio for iSeries: List Price, $79.95
    Getting Started With WebSphere Development Studio Client for iSeries: List Price, $89.00
    Getting Started with WebSphere Express for iSeries: List Price, $49.00
    WebFacing Application Design and Development Guide: List Price, $55.00
    Can the AS/400 Survive IBM?: List Price, $49.00
    The All-Everything Machine: List Price, $29.95
    Chip Wars: List Price, $29.95

    Gumbo Creates Digitally Signed PDFs from i5/OS Spool Files Recession Alert: IBM Gooses System i Maintenance Prices

    Leave a Reply Cancel reply

Volume 8, Volume 11 -- March 19, 2008
THIS ISSUE SPONSORED BY:

Help/Systems
Guild Companies
WorksRight Software

Table of Contents

  • Grouping a Union
  • Remember the Allocation
  • Stopping User from Using the System Request Menu

Content archive

  • The Four Hundred
  • Four Hundred Stuff
  • Four Hundred Guru

Recent Posts

  • N2i Gains Traction Among IBM i Newbies
  • Realizing The Promise Of Cross Platform Development With VS Code
  • 2023 IBM i Predictions, Part 3
  • Four Hundred Monitor, January 25
  • Join The 2023 IBM i Marketplace Survey Webinar Tomorrow
  • It Is Time To Have A Group Chat About AI
  • 2023 IBM i Predictions, Part 2
  • Multiple Vulnerabilities Pop Up In Navigator For i
  • Participate In The 2023 IBM i Marketplace Survey Discussion
  • IBM i PTF Guide, Volume 25, Number 4

Subscribe

To get news from IT Jungle sent to your inbox every week, subscribe to our newsletter.

Pages

  • About Us
  • Contact
  • Contributors
  • Four Hundred Monitor
  • IBM i PTF Guide
  • Media Kit
  • Subscribe

Search

Copyright © 2022 IT Jungle

loading Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.