count statement needed

Posted on 2007-10-15
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
Question by:arthrex
    LVL 75

    Accepted Solution

    SELECT Code, Name, Tour, Count(*) cnt
    FROM (

    SELECT DISTINCT Code, name, Tour
    from urTable  ) a

    GROUP BY Code, Name, Tour,

    Assisted Solution

    Select Code, Count(Distinct tour) from <tablename> group by code

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

    Assisted Solution

    select code, name, count(tour) tourcnt from <tablename> group by 1, 2

    Author Comment

    thanks all for your answers!

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
    Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
    Viewers will learn how the fundamental information of how to create a table.
    Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

    729 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

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now