Group By returns nothing?

Question

question

shiftysamr asked Oct 28, '19

Group By returns nothing?

Hello,

I have a simple inner join query that returns a happy set of results. It works great without GROUP BY clause as seen below. However when I add "GROUP BY" clause it returns nothing. I am trying to understand why. This is a query against a log table and currently there is only one entry for each CUSTOMER. in the log.

Any help would be greatly appreciated.

SELECT 
	customer.id AS custid, 
	custrate.crid, 
	DLS_pbx_tenant.tenantid, 
	DLS_pbx_tenant.mirror_exts
FROM 
	DLS_PBX_tenant, 
	customer, 
	custrate 
WHERE 
	CONVERT(INTEGER, DLS_PBX_tenant.name) = customer.id 
	AND CONVERT(INTEGER, DLS_PBX_tenant.name) = custrate.custid 
	AND custrate.rgid = 540 
	AND dls_pbx_tenant.custid IN (39866)
GROUP BY 
	customer.id, 
	custrate.crid,
	DLS_pbx_tenant.tenantid,
	DLS_pbx_tenant.mirror_exts

Result:

+--------+-------+----------+-------------+
| custid | crid  | tenantid | mirror_exts |
+--------+-------+----------+-------------+
| 37769  | 44956 | 20       | 0           |
| 37771  | 44960 | 38       | 0           |
| 39304  | 48849 | 9        | 0           |
| 39881  | 50525 | 7        | 1           |
| 39882  | 50526 | 6        | 0           |
| 39888  | 50555 | 54       | 0           |
| 39903  | 50589 | 14       | 0           |
| 39908  | 50595 | 16       | 0           |
| 39911  | 50601 | 17       | 0           |
| 39947  | 50695 | 24       | 0           |
| 39961  | 50725 | 28       | 0           |
| 39974  | 50767 | 35       | 0           |
| 39982  | 50783 | 34       | 1           |
| 40002  | 50852 | 37       | 0           |
| 40012  | 50897 | 41       | 0           |
| 40032  | 50945 | 49       | 0           |
| 40034  | 50949 | 48       | 0           |
| 40042  | 50977 | 55       | 0           |
| 40048  | 50986 | 51       | 0           |
| 40049  | 50987 | 52       | 0           |
| 40064  | 51024 | 56       | 0           |
| 40070  | 51037 | 57       | 1           |
| 40073  | 51046 | 59       | 0           |
+--------+-------+----------+-------------+

group-by grouping

3

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Oleg commented · Oct 29, 2019 at 01:18 PM

@shiftysamr If your select without a GROUP BY returns some rows then the same select with a GROUP BY will also return something. If not then there is some difference between your selects even though you believe that they are the same. The syntax is odd though, to use this way of joining tables is ill-advised and has been for many years. Also, while it is not possible to tell without knowing the actual data in the tables, matching the name column with ID column just does not look right.

By the way, it would be nice if you could shed some light on the answer to your previously posted question. Doing so might help the future users of the site looking for help to the similar questions. Plus, there is nothing wrong with being polite and providing a small feedback, i.e. yes it worked, or no it did not...

0 ·

shiftysamr commented · Oct 29, 2019 at 01:51 PM

@Oleg

I am sorry for not posting my response to the prior question sooner. I did not mean to be rude, I was just absolutely sure that I responded and I must not have sent "Send" when I typed up the answer last time.

With this new question, pardon my syntax, of course, but it still is legitimate even though I do not use "join" statements. What other information can I provide? If I run the query without the group by clause just as it is here - I get the results that I had shown.

0 ·

Oleg commented · Oct 29, 2019 at 02:31 PM

There are 4 columns in the select list and, as stated, the select returns some rows. Adding the group by the same 4 columns to same exactly select statement might reduce the number of returned records, but it will not cause the "returns nothing" scenario you describe. This only means that there is no way that the selects are the same, that is all. Also, the way the statement is spelled out does not look right, it feels that there is something wrong with it. For example, the WHERE clause includes dls_pbx_tenant.custid IN(39866), which means that the table does indeed have the column named CUSTID. If this is the case then why does the "join condition" is NAME = CUSTID?

Take your statement (with GROUP BY removed) and replace the line reading FROM with the one reading INTO #someTempTable FROM. This will create the temp table and populate it with results of the select. Now, run select (your 4 columns) from #someTempTable GROUP BY (your 4 columns). Does this "return nothing"? If it returns some rows then the selects were not identical, there were some differences.

0 ·

Answer 1 · 2019-10-29T14:51:46Z

shiftysamr answered Oct 29, '19

@Oleg, I tried to do the insert and here is what I get:

Msg 245, Level 16, State 1.
Conversion failed when converting the varchar value 'default' to data type int. (Line 2)

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 2 · 2019-10-29T14:54:27Z

shiftysamr answered Oct 29, '19

I think the problem is that I have a value 'default' in DLS_pbx_tenant.name. And this is breaking the query. Don't understand how SELECT worked in the first place.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 3 · 2019-10-29T15:16:58Z

shiftysamr answered Oct 29, '19

So the solution to my problem was to validate data in DLS_pbx_tenant.name. After adding IsNumeric() function to the WHERE clause everything worked as expected!

@Oleg, thank you for your advice, inserting the records into the table gave me a clue as to what might be wrong. Still, I don't understand why SELECT did not give me an error when I was trying to convert varchar to INT.

SELECT 
	customer.id AS custid, 
	custrate.crid, 
	DLS_pbx_tenant.tenantid, 
	DLS_pbx_tenant.mirror_exts
FROM 
	DLS_PBX_tenant, 
	customer, 
	custrate 
WHERE 
	CONVERT(INTEGER, DLS_PBX_tenant.name) = customer.id 
	AND CONVERT(INTEGER, DLS_PBX_tenant.name) = custrate.custid 
	AND custrate.rgid = 540 
	AND dls_pbx_tenant.custid IN (39866)
	AND IsNumeric(DLS_pbx_tenant.name) = 1
GROUP BY 
	customer.id, 
	custrate.crid,
	DLS_pbx_tenant.tenantid,
	DLS_pbx_tenant.mirror_exts

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question