• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 325
  • Last Modified:

MySQL Query - combining 2 row results as 1

Hi,

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.

Thanks

Mark.
0
funasset
Asked:
funasset
  • 3
  • 3
1 Solution
 
Aleksandar BradarićSoftware DeveloperCommented:
I'm typing this without checking the query:
---
SELECT
  SUM(p1.Qty) END AS Available,
  SUM(p2.Qty) END AS Pending,
  p.ProductID,
  pr.Name
FROM
  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
WHERE
  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
GROUP BY
  p.ProductID
---

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...
0
 
funassetAuthor Commented:
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.
0
 
hernst42Commented:
should be pr.ProductID instead of p.ProductID same for p.UserID
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
funassetAuthor Commented:
Hernst42,

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.
0
 
Aleksandar BradarićSoftware DeveloperCommented:
Fast and loaded with errors :( Here's the one that works. I've had to rearrange the stuff a bit:
---
SELECT
  SUM(IF(i1.Paid = 1, p1.Qty, 0)) AS Available,
  SUM(IF(i2.Paid = 0, p2.Qty, 0)) AS Pending,
  pr.ID,
  pr.Name
FROM
  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
WHERE
  pr.License = 1 AND
  p1.ID = p2.ID
GROUP BY
  pr.ID
---
0
 
funassetAuthor Commented:
Leannonn,

Thank you very very much. It works perfectly! Take the 500 points with my thanks. You have saved me hours (possibly days)!!
0
 
Aleksandar BradarićSoftware DeveloperCommented:
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 :)
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now