Group by clause does not work

Transact-SQL
1

Hello guys
This code works

select distinct(group_name), COUNT(group_name) as total, Username from PhoneNumber_Groups_Numbers group by Group_Name, Username order by Username asc

but when I add phoneid to the list, it does not work. PhoneID is the table PK

What am I doing wrong ? thanks

MI

2

Please post the query with phoneid

3 
select distinct(group_name), COUNT(group_name) as total, Username, phoneid from PhoneNumber_Groups_Numbers group by Group_Name, Username, phoneid order by Username asc
4 
SELECT group_name,
	COUNT(group_name) AS total,
	Username,
	phoneid
FROM PhoneNumber_Groups_Numbers
GROUP BY Group_Name,
	Username,
	phoneid
ORDER BY Username ASC
5

No it does not work.

Rather it gives me all the records and the count(group_name) as total returns 1 in each results :neutral_face:

6

That is to be expected. When you include the "Group_Name" column in the group by clause, the query treats each Group_Name as a separate group, and of course there will be only one Group_Name in each of those groups. So perhaps what you need is the following:

SELECT
	COUNT(group_name) AS total,
	Username,
	phoneid
FROM PhoneNumber_Groups_Numbers
GROUP BY
	Username,
	phoneid
ORDER BY Username ASC

If that does not quite give you the results you are looking for, can you post some sample data and the corresponding output you are trying to get?

The general rule to remember is that when you have an aggregate function such as COUNT, SUM, AVG etc. in the SELECT clause, every column in the select list that is not inside of one of those aggregate functions must be listed in the GROUP BY clause.

7

see attachment

The second image is what I get with the original query,the first is what i get with I add PK phoneID to it

8

Your first query has four columns in the select list, but the second image shows only two.

I am not able to discern exactly what you are looking for based on the screen shots. If you can visualize how SQL processes your request, you might be able to fix the problem yourself. To that end, suppose you have a query like shown below:

SELECT
	username,
	groupname,
	COUNT(*) AS Total
FROM
	PhoneNumber_Groups_Numbers
GROUP BY
	username,
	groupname

In this case, what SQL Server does is to walk through every row in you table, look at the values of username and groupname (the columns in the group by list), and put them into buckets that have the same username and groupname. Then, it counts the entries in each bucket and reports the count as the Total.

Given that, if you also add PhoneID to the select list, which then forces you to put PhoneID into the group by list, each bucket will have a unique combination of username, groupname and PhoneID. But there can be only one row in each of those buckets because PhoneID is the primary key, and by definition, there can be only one row in the table with a given PhoneID.

To put it another way, suppose you had only two rows in your entire table like shown below:
username = abu, phoneid = 100652, groupname = abcd
username = abu, phoneid = 222222, groupname = abcd

What is the output you are looking for in this case?

9

thanks

the output I want to achieve is something like this

username=mary, phoneid = 1121321, groupname = kajdfkajfdk
username=may, phoneid = 1121334321, groupname = kajdfkawertwre
username=ary, phoneid = 11213241, groupname = kajdfkajfdkwtwrt
username=may, phoneid = 1123421321, groupname = kajdfkajfdkwtrwtr

The above query works for me, its only when I add the PK phoneid, then it fails

thanks a lot

10

Don't you want to get a count as well? I thought that was the main issue - that you were getting count = 1 for every row in the output.

11

Sorry I forgot that.

I also meant to say, your query above works as it gets the count, but I want the phoneid to be referenced in my frontend.

thanks

12

I think we are going in circles :smiley: So, going back to my original question: Suppose you had only two rows in your table. Nothing else - just these two rows. What is the output you want to get?

username = abu, phoneid = 100652, groupname = abcd
username = abu, phoneid = 222222, groupname = abcd

If you want to get the phoneid also in the output, along with the count of rows that have the same username and groupname, perhaps this might be what you are looking for:

SELECT
	phoneid,
	username,
	groupname,
	COUNT(*) OVER (PARTITION BY username, groupname) AS Total
FROM
	PhoneNumber_Groups_Numbers

As an aside, performing these types of counts is something that SQL Server is built for, and is very good at. Almost any one of the people who answers questions on this forum would be able to write the query you need easily. The issue though is that you have to provide a correct, representative sample input data and the exact output that you are looking for, along with the logic to be used if it is not obvious from the input+output combination.