I want that my query show the total of results that obtain.

For example, if i execute this query:

SELECT user, email FROM users WHERE user = 'Daniel';

Show a third column with the total rows. With user = 'Daniel' i have a total of 13 records and that's how I want to show it:

user          email                total
Daniel        email1@gmail.com     13
Daniel        email2@gmail.com     13
Daniel        email3@gmail.com     13
Daniel        email4@gmail.com     13

Is it possible to do this?

  • Whats wrong with using COUNT? – Mark Nov 7 at 10:04
  • Where should this number come from? Can you share sample input data? – Nico Haase Nov 7 at 10:11
  • Why do you want same count repeating for all rows in a group? – Himanshu Ahuja Nov 7 at 10:12
  • I have no link to it.. . But i think this is a duplicate. This is definitely done by someone. Remember that Google is your best friend to find such a information. – Ingus Nov 7 at 10:53

try like below

 SELECT u2.user, u2.email,
(select count(*) from users u1 where u1.user=u2.user ) as total
  FROM users u2
 WHERE u2.user = 'Daniel';

in case mysql version >=8.0 then you can use count() window function

select user,email,count(*) over()  as total
from  users
     WHERE user = 'Daniel'
  • Your first query is not optimal, and a join would perform better. – Tim Biegeleisen Nov 7 at 10:24
  • Also what if there are duplicates in count – Himanshu Ahuja Nov 7 at 11:37
  • 1
    @HimanshuAhuja have you get any instruction about duplication in question ?? btw down vote is great but first know does really need to put doen vote then pl do it – Zaynul Abadin Tuhin Nov 7 at 11:48

I prefer using analytic functions here:

    COUNT(*) OVER () total
FROM users
WHERE user = 'Daniel';

If you can't use analytic functions, because you are using an earlier version of MySQL, then use a join:

    u2.cnt AS total
FROM users u1
    SELECT user, COUNT(*) AS cnt
    FROM users
    -- we could also restrict to Daniel here, but I am showing a general
    -- solution for multiple users
    GROUP BY user
) u2
    ON u1.user = u2.user
    u1.user = 'Daniel';
  • What is the benefit to using this method as opposed to the others? – Mark Nov 7 at 10:05
  • 1
    @Mark The analytic function version only requires (in theory) passing over the original table once. The correlated subquery and join versions require additional subqueries. – Tim Biegeleisen Nov 7 at 10:11
  • @Tim does over() means all columns by default in analytic functions? – Himanshu Ahuja Nov 7 at 10:16
  • OVER() specifies no partition at all, which then defaults to meaning over the entire table. Try it out yourself to see how it works. – Tim Biegeleisen Nov 7 at 10:18
  • @TimBiegeleisen does OVER() works on MariaDB also? – Ingus Nov 7 at 11:01

I dont understand why do you require the same count repeating for all rows in a group. Nevertheless the below query does the same thing you want

    Select user, email, count(distinct *) 
     () as total from table ;
  • Thanks for the negative vote ive edited though i forgot the fact that OP wanted whole count not that just one partition – Himanshu Ahuja Nov 7 at 10:18
  • I don't think MySQL supports COUNT(DISTINCT) when used as an analytic function. – Tim Biegeleisen Nov 7 at 10:36
  • Perhaps i guess thats standard and should work with analytical too – Himanshu Ahuja Nov 7 at 11:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.