MySQL Query - combining 2 row results as 1

Posted on 2007-08-09
Last Modified: 2008-01-09

I am developing a MySQL database (and am relatively new to MySQL) and have a problem in that I need to group certain like parts of a query result as a single row result (instead of two rows).

I think I am almost there with the results... Here they are...

Record Available Pending ProductID Name
1                              1                5      Product5
2                3                              5      Product5
3                              6                7      Product7
4                5                              7      Product7
5                2                 8      Product8
6                             1                 9      Product9
7                2                              9      Product9

However, this is how I want them to look...

Record Available Pending ProductID Name
1                  3           1             5         Product5
2                  5           6             7         Product7
3                  2             8         Product8
4                  2           1             9         Product9

As you can see I want to group the Available and Pending results for the same product on the same row (i.e. the same record).

Here is my query script...

SELECT CASE i.Paid WHEN 1 THEN SUM(p.Qty) END AS Available, CASE i.Paid WHEN 0 THEN SUM(p.Qty) END AS Pending, p.ProductID, pr.Name
FROM funasset.products pr, funasset.purchases p JOIN funasset.invoices i ON i.ID = p.InvoiceID
WHERE p.UserID = 7 AND pr.ID = p.ProductID AND pr.License = 1
GROUP BY p.ProductID, i.Paid

Sorry if I am a little vague but this is my first post. Let me know if you want further information.

I hope someone can help.


Question by:funasset
    LVL 17

    Expert Comment

    I'm typing this without checking the query:
      SUM(p1.Qty) END AS Available,
      SUM(p2.Qty) END AS Pending,
      funasset.products pr
      funasset.purchases p1
      funasset.purchases p2
      JOIN funasset.invoices i1 ON i1.ID = p1.InvoiceID
      JOIN funasset.invoices i2 ON i2.ID = p2.InvoiceID
      p.UserID = 7 AND
      pr.ID = p1.ProductID AND i1.Paid = 1 AND
      pr.ID = p2.ProductID AND i2.Paid = 0 AND
      pr.License = 1

    I'll be able to test run it on the server soon, so I'll get back to you if I've made any errors...

    Author Comment

    Hi Leannonn,

    Thanks for the fast response! However, I have added the query and it complained about the following...

    1. END (in AS Available and AS Pending) - I removed both ENDs and this error went away.
    2. Complains about p.ProductID not being recognised

    I look forward to your reply.
    LVL 48

    Expert Comment

    should be pr.ProductID instead of p.ProductID same for p.UserID

    Author Comment


    ProductID and UserID are referenced in the Purchases table not the Products table so changing p to pr (Products table) will not work. Also discovered commas (,) are required after funasset.products pr
     and funasset.purchases p1. However, I am still getting p.ProductID Unrecognised.

    Thanks anyway.
    LVL 17

    Accepted Solution

    Fast and loaded with errors :( Here's the one that works. I've had to rearrange the stuff a bit:
      SUM(IF(i1.Paid = 1, p1.Qty, 0)) AS Available,
      SUM(IF(i2.Paid = 0, p2.Qty, 0)) AS Pending,
      funasset.products pr
      LEFT JOIN funasset.purchases p1 ON p1.ProductID = pr.ID AND p1.UserID = 7
      LEFT JOIN funasset.purchases p2 ON p2.ProductID = pr.ID AND p2.UserID = 7
      LEFT JOIN funasset.invoices i1 ON i1.ID = p1.InvoiceID AND i1.Paid = 1
      LEFT JOIN funasset.invoices i2 ON i2.ID = p2.InvoiceID AND i2.Paid = 0
      pr.License = 1 AND
      p1.ID = p2.ID

    Author Comment


    Thank you very very much. It works perfectly! Take the 500 points with my thanks. You have saved me hours (possibly days)!!
    LVL 17

    Expert Comment

    I'm glad I could help :)

    > Take the 500 points with my thanks.

    You have to accept my answer for me to get the points :)

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Introduction In this article, I will by showing a nice little trick for MySQL similar to that of my previous EE Article for SQLite (, A SQLite Tidbit: Quick Numbers Table Generation (…
    Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now