count different values and group at the same time

Asked

Viewed 100 times

0

I have a table with several cities and neighborhoods, I need to return how many neighborhoods are cited by city.

would use Count and distinc ?

Tabela esperada

sql count distinct sum sqlfiddle

  • How is your table that stores this information?

  • 1

    CREATE TABLE tb_listings ( "advertiser_id" varchar, "listing_id" varchar, "portal" varchar, "city" varchar, "Neighborhood" varchar);

1 answer

4


One possibility is to use count, distinct and group by

This query presents the number of neighborhoods per city and neighborhood.

SELECT   city, 
         neighborhood, 
         count(neighborhood) as qtd_bairros 
FROM     <nomenclatura da tabela> 
GROUP BY city, 
         neighborhood 
ORDER BY city, neighborhood DESC

This query presents the number of neighborhoods per city:

SELECT city, 
       count(DISTINCT neighborhood) qtd_bairros 
FROM   <nomenclatura da tabela> 
GROUP  BY city 
ORDER  BY city DESC

  • So this account returns the number of lines per city, I needed it per neighborhood and city in the same table ? I can do this?

  • Good , has helped me clear up the idea a doubt in "Count " I can count more than one column ? there told neighborhood needed to count nbairro and city, for example how many times appeared holy Andre

  • @Oak, city quantity by city and neighborhood?

  • Imagine 3 columns, 1 with numeors of times that that city appeared, 2 with number of neighborhoods how many times it appeared and 3 with the percentage among them. I need to answer what percentage of that neighborhood before the city

  • @Carvalho, if it is not a problem to present the quantity more than once for each record, one possibility is to put the result of a subquery to a column. SELECT city, Neighborhood, Count(Neighborhood) as qtd_neighborhood, (select Count(city) FROM <table nomenclature> WHERE city = tbl.city) as qtd_city FROM <table nomenclature> tbl GROUP BY city, Neighborhood ORDER BY city, Neighborhood DESC;

  • Thank you very much, in the last column I compare percentage of real estate per neighborhood I can use this formula : ( 100. * Count() / sum(Count()) over () decimals(10,2)) there is something cleaner ?

Show 1 more comment

Browser other questions tagged sql count distinct sum sqlfiddle

You are not signed in. Login or sign up in order to post.