Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

count statement needed

Posted on 2007-10-15
4
Medium Priority
?
249 Views
Last Modified: 2010-05-18
hello experts,

i have a table like the one below
i need to know for each  code how many different tours are existing.

code  name    tour
100    peter     4
100    peter     4
100    peter     4
330    peer      3
330    peer      3
330    peer      2

the statement should give me this information
100 --> 1 because only one tour
330 --> 2 because two differnt entries

thanks a lot
0
Comment
Question by:arthrex
4 Comments
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 680 total points
ID: 20080249
SELECT Code, Name, Tour, Count(*) cnt
FROM (

SELECT DISTINCT Code, name, Tour
from urTable  ) a

GROUP BY Code, Name, Tour,
0
 

Assisted Solution

by:kikuchiyosesa
kikuchiyosesa earned 660 total points
ID: 20080263
Select Code, Count(Distinct tour) from <tablename> group by code

Replace <tablename> with whatever table you are querying.
0
 
LVL 16

Assisted Solution

by:SQL_SERVER_DBA
SQL_SERVER_DBA earned 660 total points
ID: 20081483
select code, name, count(tour) tourcnt from <tablename> group by 1, 2
0
 

Author Comment

by:arthrex
ID: 20118651
thanks all for your answers!
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

810 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