473,288 Members | 1,794 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,288 software developers and data experts.

Adding GROUP BY empties the result set?

Using MySQL 5.0.15

I am no expert so maybe I am deeply misundertanding how this should
work. I have a relatively complex quey with joins and a subquery (it is
probably far from optimized, but that is another issue; right now I am
more interested in correctness than speed). If I do not put a GROUP BY
clause then the query returns a number of rows, as expected. If I add a
GROUP BY clause, I get an empty set! My understanding of the semantics
is that GROUP BY should act on the same set that would have been
returned by the query without it, so it does not make sense to me that
the set becomes completely empty. Depending on the column(s) one groups
on, in the "worst" case there should be one row left. I did notice by
using EXPLAIN that MySQL does not execute the query the same way with
and without the GROUP BY, but that should only affect the speed of the
query, not the end result.

Here is my query (without the GROUP BY):

SELECT
transaction_status.transaction_id as this_id,
person_id, payment_method, status, transaction_item.sale_item_id,
transaction_timestamp, YEAR(transaction_timestamp) as year
FROM transaction_status
LEFT JOIN transaction USING (transaction_id)
LEFT JOIN transaction_item USING (transaction_id)
LEFT JOIN sale_item USING (sale_item_id)
WHERE
(TRUE,transaction_status_id) =
(SELECT (status = "Completed"), transaction_status_id
FROM transaction_status
WHERE transaction_id = this_id
ORDER BY transaction_timestamp desc
LIMIT 1)

Cheers,

Jean

Nov 23 '05 #1
3 2249
At least I solved my problem by placing the subquery in the FROM
clause, but I am still curious about my original question.

Cheers,

Jean

Nov 23 '05 #2
lagj wrote:
Using MySQL 5.0.15 ...
If I do not put a GROUP BY
clause then the query returns a number of rows, as expected. If I add a
GROUP BY clause, I get an empty set! ...
Here is my query (without the GROUP BY):

....

The question about the GROUP BY behavior is hard to answer without
knowing which field you were using in the GROUP BY clause!

Also, typically you'd use GROUP BY if you have an aggregate function in
your SELECT list, such as COUNT(), SUM(), MAX(), etc. Read the page
http://dev.mysql.com/doc/refman/5.0/...functions.html

There are some mentions that some aggregate functions ignore NULLs.
So if you are GROUPing BY a field that is always NULL (because of your
outer joins), it could yield no results.

Regards,
Bill K.
Nov 23 '05 #3
Bill Karwin wrote:
The question about the GROUP BY behavior is hard to answer without
knowing which field you were using in the GROUP BY clause!


Good point Bill. Yeah, I am at least aware of the point of using GROUP
BY... Here was my original query with the GROUP BY (the one that
returned an empty set):

SELECT
transaction_status.transaction_id as this_id,
person_id, payment_method, status, transaction_item.sale_item_id,
transaction_timestamp, YEAR(transaction_timestamp) as year,
SUM(quantity * (transaction_item.sale_item_id IN (8,9,12))) as
adultBanquet,
SUM(quantity * (transaction_item.sale_item_id = 13)) as childBanquet,
SUM(quantity * (transaction_item.sale_item_id IN (8,9,10,11))) as
convention,
SUM(quantity * (transaction_item.sale_item_id IN (14,15))) as dayPass

FROM transaction_status
LEFT JOIN transaction USING (transaction_id)
LEFT JOIN transaction_item USING (transaction_id)
LEFT JOIN sale_item USING (sale_item_id)
WHERE
(TRUE,transaction_status_id) =
(SELECT (status = "Completed"), transaction_status_id
FROM transaction_status
WHERE transaction_id = this_id
ORDER BY transaction_timestamp desc
LIMIT 1)
GROUP BY year, person_id

Neither quantity nor sale_id_id can be NULL, so I don't think your
possible explanation is it. Thanks for trying. It's not that important
to me now anyway since I found another way.

The query that gave me the result I wanted is:

SELECT transaction_status.transaction_id,
person_id,
payment_method, status,
transaction_item.sale_item_id,quantity,transaction _item.amount,
transaction_timestamp, YEAR(transaction_timestamp) as year,
SUM(quantity * (transaction_item.sale_item_id IN (13,14,17))) as
num_banq,
SUM(quantity * (transaction_item.sale_item_id = 18)) as
num_banq_child,
SUM(quantity * (transaction_item.sale_item_id IN (13,14,15,16)))
as convention,
SUM(quantity * (transaction_item.sale_item_id IN (19,20))) as
dayPass

FROM transaction_status
INNER JOIN (SELECT
transaction_id,MAX(transaction_timestamp) as timetag
FROM transaction_status
GROUP BY transaction_id) as T USING (transaction_id)
LEFT JOIN transaction USING (transaction_id)
LEFT JOIN transaction_item USING (transaction_id)
LEFT JOIN sale_item USING (sale_item_id)
WHERE
transaction_status.transaction_timestamp = T.timetag
AND
status = "Completed"
AND
YEAR(transaction_timestamp) = YEAR(NOW())
GROUP BY person_id

Cheers,

Jean

Nov 23 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: Stephen Miller | last post by:
Hi, I am trying to add a staggered running total and average to a query returning quarterly CPI data. I need to add 4 quarterly data points together to calculate a moving 12-month sum (YrCPI),...
4
by: Chad Richardson | last post by:
I've always been mistified why you can't use a column alias in the group by clause (i.e. you have to re-iterate the entire expression in the group by clause after having already done it once in the...
2
by: swhite76 | last post by:
I have a section of code that adds some double values and gives an incorrect result. This occurs with data that isn't really waht I would call high precision. An example is the following code...
5
by: surrealtrauma | last post by:
the requirement is : Create a class called Rational (rational.h) for performing arithmetic with fractions. Write a program to test your class. Use Integer variables to represent the private data...
1
by: Asha | last post by:
Hello, below is a xml file which Iā€™m working with. I want my result to be like this <group name="grp 4"> <report> <name>MTIS_1</name> </report> <report> <name>MTIS_1</name> </report />
4
by: rodchar | last post by:
hey all, what's the best way to automate adding a user to local group on the computer and have it be part of administrators? thanks, rodchar
17
by: Sri | last post by:
How do you add an n-bit number in C? Regards, Sri
1
by: Janroel | last post by:
Hi all, I have 2 webservers running IIS 6.0, and I use forms with get and post. When a form is sent, and the user hits the browser back button: Webserver 1 redisplays de original form data in...
5
by: =?Utf-8?B?anVzdGluc2FyYWNlbm8=?= | last post by:
I'm using .net 2.0 and am having a problem adding doubles: double x = 63881.97 + 34297.98; The result of this addition in Visual Studio is 98179.950000000012 This is obviously the incorrect...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.