Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2014
    Posts
    2

    Question Unanswered: Multiple count with different limit

    Is it possible to use 2 COUNT in same question with different limit. I want to do something like this.
    Select count(name) AS n1 from (select name from a_table where name like 'B%' LIMIT 5) t
    Select count(name) AS n2 from (select name from a_table where name like 'B%' LIMIT 10) x
    I want to find out how many names are starting with B in the first 5 rows AND in the 10 first rows.
    Hope someone can help me.
    Thanks.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    MySQL doesn't support the SQL feature (the OVER clause) needed to do this directly, but you can sometimes kludge it.

    The LIMIT clause in MySQL is somewhat arbitrary unless you use an ORDER BY to govern its affect... There is no ORDER BY clause in your sample code, but is there one in your production code? The kludge that I'm proposing requires a unique way to order the rows so that every row has different, comparable values. Can you do that with your data?

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Mar 2014
    Posts
    2
    Thanks for your replay.
    I solved it like this.
    select
    (SELECT COUNT(*) FROM (select * FROM lopp WHERE horse LIKE 'name%' ORDER BY datum desc LIMIT 3) o WHERE placering = 1) AS tre1,
    (SELECT COUNT(*) FROM (select * FROM lopp WHERE horse LIKE 'name%' ORDER BY datum desc LIMIT 4) p WHERE placering = 1) AS fyra1
    FROM lopp GROUP BY tre1

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •