get a distinct row along with it values

user_8139278 0
Viewed 40

so i wanted to get the expected result, while using a sub query but my answer isnt going to plan, below is the code i have and the result alongside my expected result

SELECT "Invoice"."BillingCountry", "Genre"."Name", COUNT("Track"."TrackId") FROM "Track"
INNER JOIN "Genre"
ON "Track"."GenreId" = "Genre"."GenreId"
INNER JOIN "InvoiceLine" 
ON "Track"."TrackId" = "InvoiceLine"."TrackId"
INNER JOIN "Invoice"
ON "InvoiceLine"."InvoiceId" = "Invoice"."InvoiceId"
WHERE "Invoice"."BillingCountry" in  ('USA', 'Canada', 'Brazil', 'france', 'Germany')
GROUP BY "Genre"."GenreId", "Invoice"."BillingCountry"
ORDER BY COUNT("Track"."TrackId") DESC 
LIMIT 10


billingcountry | name |  count
       usa       bat      50
       canada    bat      43
       usa       car      40
       usa       toy      39
       germany   bat      36
       france    bat      34
       canada    bike     20
       usa       fing     15
       brazil    bat      14
       france    shoe     10

expected result

country    | item |  count
   usa       bat      50
   canada    bat      43
   germany   bat      36
   france    bat      34
   brazil    bat      14

i know there are similar solutions here, but i cant just get them to work, will be grateful if someone can help

postgresql sql
2 Answers

You can use sub query and max as follows:

Select "BillingCountry", "Name", max(cnt) as sm_result from
(SELECT "Invoice"."BillingCountry", "Genre"."Name", 
       COUNT("Track"."TrackId") FROM "Track" as cnt
INNER JOIN "Genre"
ON "Track"."GenreId" = "Genre"."GenreId"
INNER JOIN "InvoiceLine" 
ON "Track"."TrackId" = "InvoiceLine"."TrackId"
INNER JOIN "Invoice"
ON "InvoiceLine"."InvoiceId" = "Invoice"."InvoiceId"
WHERE "Invoice"."BillingCountry" in  ('USA', 'Canada', 'Brazil', 'france', 'Germany')
GROUP BY "Genre"."GenreId", "Invoice"."BillingCountry"
ORDER BY COUNT("Track"."TrackId") DESC 
LIMIT 10) t
Group by "BillingCountry", "Name"
user_115656290

You seem to want the most popular genre per country. If so, use DISTINCT ON:

SELECT DISTINCT ON (i."BillingCountry") i."BillingCountry", g."Name", COUNT(*)
FROM "Track" t JOIN
     "Genre" g
     ON t."GenreId" = g."GenreId" JOIN
     "InvoiceLine" il
     ON t."TrackId" = il."TrackId" JOIN
     "Invoice" i
     ON il."InvoiceId" = i."InvoiceId"
WHERE i."BillingCountry" in  ('USA', 'Canada', 'Brazil', 'France', 'Germany')
GROUP BY g."GenreId", i."BillingCountry"
ORDER BY i."BillingCountry", COUNT(*) DESC ;

Note how table aliases make the query easier to write and read. I would also fix the table definitions so you don't have to escape table and column names -- having to escape them is a really, really bad practice.

user_11440350
Related