?
Solved

How to format group by sql statement?

Posted on 2007-11-15
2
Medium Priority
?
808 Views
Last Modified: 2010-04-21
I would like the following output where each name is listed only once using sql 2000:

Name       count
name       4
name 2    1

I am getting:
Name       count
name       4
name       3
name 2    1
 
sql code I am using:
select co.name, count(n.attitude)  as Total
from customer c, coment n, subcompany co
group by co.name, c.indx, n.indx, n.attitude
having c.indx = n.indx and  c.company = co.code and n.indx = c.company

0
Comment
Question by:swansonplace
2 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 20290784
select co.name, count(n.attitude)  as Total
from customer c
join coment n
  on c.indx = n.indx
 and n.indx = c.company
join subcompany co
  on c.company = co.code
group by co.name

0
 

Author Closing Comment

by:swansonplace
ID: 31409372
Thank you.  That was clean and it worked.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question