mysql query (multiple counts)

Question

With this query:

SELECT abc, def, COUNT(*) as c 
FROM xpto 
GROUP BY abc, def
ORDER BY abc, c DESC

I have this result:

xpto

abc | def | c

x | c_1 | 8
...
y | a_2 | 4
y | a_1 | 2
y | a_3 | 1
…
z | b_2 | 7
z | b_1 | 3
...

I wish to have this result (ordered by number of entries for each abc and field c):

y | a_2 
y | a_1
y | a_3

z | b_2
z | b_1

x | c_1

Can a SQL guru help me with this task? I've seen this example SQL Help: Counting Rows in a Single Query With a Nested SELECT, is this a good solution (nested select), or there is no other way to do?

Thanks in advance

I am not sure, but what if you change ORDER BY abc, c DESC, to ORDER BY abc ASC, c DESC ? Am i right, that you want to order by abc first and then by c? Or you just dont' want to display c in result? — Uriil, Aug 02 '13 at 15:26
Can you provide sample data? It seems to me that your query should give you what you want already. — fancyPants, Aug 02 '13 at 15:54
I don't want order abc by name of entries but by number of abc entries with the same name. I just wanted to spend the first result for the second. Thanks in advance — x13, Aug 02 '13 at 16:07
I edited the example in question to better understand the ultimate goal. — x13, Aug 04 '13 at 16:23

score 1 · Answer 1 · edited Apr 25 '16 at 17:07

Use a nested query:

SELECT abc, def 
FROM (
  SELECT abc, def, COUNT(*) as c 
  FROM xpto 
  GROUP BY abc, def
) alias
ORDER BY abc, c DESC

(from comment) The challenge is not order abc alphabetically but by number of entries with the same value. I edited the example in question to better understand the ultimate goal.

OK, then just change the order of columns in the ORDER BY clause:

SELECT abc, def, c
FROM (
  SELECT abc, def, COUNT(*) as c 
  FROM xpto 
  GROUP BY abc, def
) alias
ORDER BY c DESC, abc;

The above query gives aggregate valuse (i.e. Unique rows - one row per each pair of values).

If you wish to list all rows from the table ordered by number of entries, try this query:

SELECT abc, def, 
       ( SELECT COUNT(*) FROM xpto x1
         WHERE ( x.abc, x.def ) = (x1.abc, x1.def)
        ) as c
FROM xpto x
ORDER by c desc, abc
;

This query displays columns abc, def + count. If you want to display only abc + def, without the value of count, then try this query:

SELECT abc, def
FROM xpto x
ORDER by  (  SELECT COUNT(*) FROM xpto x1
             WHERE x.abc = x1.abc AND x.def = x1.def
           ) desc, 
           abc
;

Look at sqlfiddle demo that demonstrates these 3 queries.

Pay attention to the condition used in the second query (in the dependent subquery):

WHERE ( x.abc, x.def ) = (x1.abc, x1.def)

This syntax is complaint with ANSI SQL, however may not work on some older version of MySQL. In that case, change this condition to its equivalent version:

WHERE x.abc = x1.abc AND x.def = x1.def

kordirko the challenge is not order abc alphabetically but by number of entries with the same value. I edited the example in question to better understand the ultimate goal. Thanks in advance — x13, Aug 04 '13 at 16:22
@x13 I edited my answer and pasted new examples how this problem could be solved, best regards. — krokodilko, Aug 04 '13 at 17:30

Nalaka526 · Accepted Answer · 2013-08-04T17:15:05.197

1

Check this query

SELECT abc, def, COUNT(*) as c,
  (SELECT COUNT(1) FROM (
        SELECT abc, def, COUNT(*) as c
        FROM xpto A
        GROUP BY abc, def
    ) B
  GROUP BY ABC
  HAVING C.abc=B.abc
  ) ic
FROM xpto C
GROUP BY abc, def
ORDER BY ic DESC, c DESC

This may not be the best way to achieve this, but it works (Check this SQL Fiddle)

edited Aug 04 '13 at 17:15

answered Aug 04 '13 at 16:25

Nalaka526

11,278
21
82
116

mysql query (multiple counts)

2 Answers2