Count and group by - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Count and group by

Author

Topic

olibara
Yak Posting Veteran

94 Posts

Posted - 2009-08-14 : 04:55:59

Hello it seems that I can't use the same Count / group by query from MySql to MSSQL

In MySql, this works fine but NOT in MS SQL

SELECT 
count(*),
PersID AS PersID,
PersNom AS Nom,
PersPrenom AS Prenom,
PersSexID AS Sex,
PaysNomFR AS Nationalite,
PersGSM AS Mobile,
PersTel AS Tel,
PersEmail AS Email
FROM Personnes_tbl
INNER JOIN Link_Contacts_tbl ON PersID = CtacPersID 
LEFT OUTER JOIN Pays_tbl ON PersNationID = PaysID 
group by (PersID)

In MS SQL I have to do it so (duplicates all fields in the group by) : I can't believe it, this is tedious and unusefull. Is there any other solution

Thanks for any help

SELECT   
count(*),
PersID AS PersID,
PersNom AS Nom,
PersPrenom AS Prenom,
PersSexID AS Sex,
PaysNomFR AS Nationalite,
PersGSM AS Mobile,
PersTel AS Tel,
PersEmail AS Email
FROM Personnes_tbl
INNER JOIN Link_Contacts_tbl ON PersID = CtacPersID 
LEFT OUTER JOIN Pays_tbl ON PersNationID = PaysID 
group by PersID,
PersNom,
PersPrenom,
PersSexID,
PaysNomFR,
PersGSM,
PersTel,
PersEmail

Ifor
Aged Yak Warrior

700 Posts

Posted - 2009-08-14 : 07:01:27

I do not know MySQL but maybe:


SELECT -- DISTINCT ???
	COUNT(*) OVER (PARTITION BY PersID),
	PersID AS PersID,
	PersNom AS Nom,
	PersPrenom AS Prenom,
	PersSexID AS Sex,
	PaysNomFR AS Nationalite,
	PersGSM AS Mobile,
	PersTel AS Tel,
	PersEmail AS Email
FROM Personnes_tbl
	INNER JOIN Link_Contacts_tbl ON PersID = CtacPersID 
	LEFT OUTER JOIN Pays_tbl ON PersNationID = PaysID

olibara
Yak Posting Veteran

94 Posts

Posted - 2009-08-14 : 07:11:38

Hello

Thanks for the suggestion, I've tryed it but got the message :

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'OVER'.

olibara
Yak Posting Veteran

94 Posts

Posted - 2009-08-14 : 07:43:33

This query is working :
SELECT
PersID AS PersID,
PersNom AS Nom,
PersPrenom AS Prenom,
PersSexID AS Sex,
PaysNomFR AS Nationalite,
PersGSM AS Mobile,
PersTel AS Tel,
PersEmail AS Email
FROM Personnes_tbl
INNER JOIN Link_Contacts_tbl ON PersID = CtacPersID
LEFT OUTER JOIN Pays_tbl ON PersNationID = PaysID

This query is working too

SELECT PersID,
count(PersID) as cnt
FROM Personnes_tbl
INNER JOIN Link_Contacts_tbl ON PersID = CtacPersID
group by PersID

How can I combine those two query to get the Count in the first one ?

Thanks for your help !

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-08-14 : 08:27:31

I'm curious.

What does MYSQL return with this?


CREATE TABLE #foo (
	[firstname] NVARCHAR(255)
	, [surname] NVARCHAR(255)
	)

INSERT #foo ([firstName], [surname])
      SELECT 'John', 'Smith'
UNION SELECT 'John', 'Doe'

SELECT
	[firstName]
	, [surname]
FROM
	#foo
GROUP BY
	[firstName]

It absolutely makes sense to me that this code should be invalid. (you haven't specified an aggregate function for the surname column.

What does MySql do with that?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-08-14 : 08:32:11

Also -- does this work for you?


SELECT 
	pers.PersID AS PersID,
	pers.PersNom AS Nom,
	pers.PersPrenom AS Prenom,
	pers.PersSexID AS Sex,
	pays.PaysNomFR AS Nationalite,
	pers.PersGSM AS Mobile,
	pers.PersTel AS Tel,
	pers.PersEmail AS Email,
	cnt.[cnt] AS Instances
FROM
	Personnes_tbl AS pers
	INNER JOIN Link_Contacts_tbl AS conts ON pres.PersID = conts.CtacPersID 
	LEFT OUTER JOIN Pays_tbl AS pays ON persPersNationID = pays.PaysID 

	INNER JOIN (
		SELECT
			PersID AS [persId],
			count(PersID) as cnt 
		FROM
			Personnes_tbl AS pt
			INNER JOIN Link_Contacts_tbl AS lc ON pt.PersID = lc.CtacPersID 
		group by
			PersID
		)
		AS cnt ON cnt.[persId] = pers.[presId]

but personally I'd just put the columns in the GROUP BY clause. It makes sense that you need to.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

olibara
Yak Posting Veteran

94 Posts

Posted - 2009-08-14 : 09:27:53

Thank you Charlie
Your last suggestion works fine !
This is the syntax I was looking for GREAT !
Fot the question about MySql
MySql will probably return 'John', 'Smith' because it takes the first founded record relevant for the group by clause

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-08-14 : 10:41:53

So it always does a silent MIN() aggregate function on the non grouped by columns?

A big difference in approach then.

I think I like SQL Servers implementation better. If only because there's no way that you could just miss out a line in the group by clause and have the sql still run but do something you weren't wanting.

I can see why, if you are used to MySql behaviour this might seem irritating though.

Cheers for the info.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

cranky franky

12 Posts

Posted - 2009-08-14 : 13:51:39

I have a similar issue and I'm new to all this. I understand that the COUNT function requires inclusion of all the fields in teh select to be included in the groupby. That really seems silly...at least to me. I have 10 fields in a select, several are manipulated datatypes with AS alias. I wish to get a simple total rows produced. hence I'm intrigued by your query that worked by nesting the count in a join, but also by your comment "but personally I'd just put the columns in the GROUP BY clause. It makes sense that you need to." Would you mind elaborating a bit on why this is needed and why your think it makes sense? I'm trying to get my head in tune with how this thing "thinks." scary. hehe Oh I posted my question in another thread "count function in stored procedure - newbie" but got no responses. Then read this one. Thanks for any help.

cranky franky

Franklin

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-08-17 : 04:44:52

Hi Franklin,

OK consider this data:


FirstName | Surname | NiNumber
----------+---------+----------
David     | James   | JS234523A
Frank     | Booth   | AS453421B
Mike      | Cat     | VF564345N
David     | Simms   | AD341212A
Mike      | Lemon   | FS453212Y

-- So for the follwing examples this is the dataset


DECLARE @foo TABLE (
	[firstName] VARCHAR(255)
	, [surname] VARCHAR(255)
	, [NiNumber] VARCHAR(255)
	)

INSERT @foo ([firstName], [surname], [NiNumber])
      SELECT 'David', 'James', 'JS234523A'
UNION SELECT 'Frank', 'Booth', 'AS453421B'
UNION SELECT 'Mike', 'Cat', 'VF564345N'
UNION SELECT 'David', 'Simms', 'AD341212A'
UNION SELECT 'Mike', 'Lemon', 'FS453212Y'

So for your example lets say we want a COUNT over First name...

So this is OK


SELECT
	[firstName]
	, COUNT([firstName])
	, COUNT([surname])
FROM
	@foo
GROUP BY
	[firstName]

And produces results like this


firstName	(No column name)	(No column name)
David	2	2
Frank	1	1
Mike	2	2

So there are 2 entries for David and Frank in the Group by but only one line for each is returned.

Now over both Firstname and surname


SELECT
	[firstName]
	, COUNT([firstName])
	, COUNT([surname])
FROM
	@foo
GROUP BY
	[firstName]
	, [surname]

Results ==
firstName	(No column name)	(No column name)
Frank	1	1
Mike	1	1
David	1	1
Mike	1	1
David	1	1

Because there are unique entries for surname there will be 2 lines returned for Frank and Mike.

Now if you tried to include the surname column in the first query like this:


SELECT
	[firstName]
	, [surname]
	, COUNT([firstName])
	, COUNT([surname])
FROM
	@foo
GROUP BY
	[firstName]

You get this error


Msg 8120, Level 16, State 1, Line 17
Column '@foo.surname' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Now this absolutely makes sense as how is the query engine supposed to know which aggregate function (SUM, MAX, MIN, COUNT, etc) that you wished performed over the surname column? The Original poster said that in MySql he thinks that the query engine will do an implicit MIN() on the column. I don't like that because it seems that there is the possibility that you could make a typo mistake (missing out a group by column) and the query engine will still accept the query but give you results you did not want.

It seems in MySql there is a more rough and ready approach to things. However, my experience in mySql is very limited.

I hope this helps you. If you have a specific scenario then post it here. Include sample data and expected output if possible.

Regards,

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

cranky franky

12 Posts

Posted - 2009-08-17 : 18:31:56

First, thank you for the time and great example. I see the logic and it makes sense that the query has to give some guidance on what to apply to what. So next step is....rather than post all of my "tests" and comments/questions about how to combine group by with count...below is the query edited for brevity and some data, with my core question. I'll play dumb and just see what you think should happen. Again, this seems like it should be really easy and I'm feeling a bit stupid for not being able to figure this out. Sorry for such a basic question.

SELECT (g.last_name + g.first_name) as Name,s.item,p.pod_id,
a.phone1,g.text_1 AS Allergies
FROM b_sched AS s
LEFT OUTER JOIN guests AS g ON s.guest_no = g.guest_no
LEFT OUTER JOIN address AS a ON g.parent_no = a.guest_no
LEFT OUTER JOIN pods AS p ON s.item = p.pod_id
WHERE s.start_time BETWEEN '2009-08-10 07:00:00'and '2009-08-10 15:00:00'
order by p.pod, s.item, g.last_name

this yields:

Name pod_id item phone1 Allergies
------------- ------- ------ ------------ -----
bill jones pod1 black 555-555-5555 no
frank smith pod1 black 444-444-4444 yes
suzy willis pod1 blue 333-333-3333 no
kathy stout pod2 green 222-222-2222 no
barry shultz pod3 red 111-111-1111 yes

This works but the order by yields the dups for pod_id(which I don't want - ugly on the report) and I don't get a total row count.
The pseudo-code for what I want is to:
GROUP BY p.pod_id with total rows in each group,
ORDER BY s.item, Name and give me a grand total of all rows at the bottom

which ideally would produce this:

Pod Name item phone1 Allergies
---- ------------- ------ ------------ -----
pod1 bill jones black 555-555-5555 no
frank smith black 444-444-4444 yes
suzy willis blue 333-333-3333 no
total in pod = 3

pod2 kathy stout green 222-222-2222 no
total in pod = 1

pod3 barry shultz red 111-111-1111 yes
total in pod = 1
total for all pods = 5

I have 6 other fields that are in the select statement that are dates, times, additional phone#'s, parent names, etc. I left those out for simplicity and they do not affect the concept. I've tried all sorts of variations on where to use COUNT and GROUP BY with either errors or output I did not want. I have not tried nesting a select as you did in one example above.

Franklin

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-08-18 : 05:35:28

You are running into problems because this is a presentation issue and you should be doing this in the front end application. SQL isn't designed for this and you have to perform dirty dirty hacks to get the output you are after

(I've got the most experience in java so this might be a bit java centric...)

If you just return the raw results from your query as it is to a reportGenerator class or similar then loop through the results writing them to whatever in the usual way then it should be trivially easy to tally the number of rows returned as the "total for all pods" Also if you had a check variable it will also be very easy to keep track of how many rows for for each distinct podnumber.

What's your front layer made of?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

cranky franky

12 Posts

Posted - 2009-08-18 : 11:30:14

ahhh I was wondering about this. I was going to ask this very question in another thread! :-)
Being new to this, I'm not up to speed yet on how much the query should do versus how much the presentation application should do. Obviously knowing the capabilities/limitations of your applications help and I'm learning that at the same time as the SQL. Bit overwhelming at times. I'm not an IT person...work in another department but have some programming skills from other "hobbies" and wanted to be able to build my own reports. We have installed MS Reporting Services and I'm working within their Visual Studio Suite.

I've been able to "group by" using the designer tools but still have issues with counting rows, so figured I'd try it on the query side. I was "handed" this project which started as a stored procedure, which has several datasets with queries. Hence I'm still plodding through that stuff to understand why that method vs another. Lots to learn but your responses have been a great help. This stuff is fascinating! Thank you very much for your time and help.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-08-19 : 05:01:08

As a rule of thumb:

Use SQL to generate the actual data of the reports. Return the rows in the simplest logical and consistent way (preserving data types if possible).

That means that things like pivoting data or Row counting should be done in the front end (your scenario for example is a perfect example of this)

Sure, you *can* do both those things in SQL but generally, you shouldn't if you can do it in your application layer. You app layer is going to better at it and more flexible.

Good luck and keep asking questions.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources