Question 298497 in Mysql Question posted in
The official MySQL documentation can be found here.

Count MYSQL select only rows with max value

I am trying to get a count of the below-noted SQL query. It will output the max value of the scan_freq column and I want to get a count of the rows of it. eg: The below query returned 1096

SELECT barcode, MAX(scan_freq)
FROM dr_scan
GROUP BY barcode

Usually, I can get a count of the dr_scan table by this query

SELECT COUNT(*) as total FROM dr_scan

enter image description here

So I want to do the same for the below query (count how many raws returned)

SELECT barcode, MAX(scan_freq)
    FROM dr_scan
    GROUP BY barcode

I tried this and it will not be giving the output I expected

SELECT COUNT(*) AS total, barcode, MAX(scan_freq)
FROM dr_scan
GROUP BY barcode
Tags:

2

Answers


  1. 0 
    SELECT barcode, COUNT(barcode) as barcode_total, MAX(scan_freq) as maxfreq
FROM dr_scan
GROUP BY barcode
    Login or Signup to reply.
  2. 0

    The number of rows returned by a query with GROUP BY column without a WHERE, HAVING or LIMIT clause is simply the number of DISTINCT values of that column. So you can get the result you want by:

    SELECT COUNT(DISTINCT barcode) AS row_count
FROM dr_scan

    If you have clauses which may restrict some rows from the output, you can do this the hard way by using your query as a subquery:

    SELECT COUNT(*) AS row_count
FROM (
  SELECT barcode, MAX(scan_freq)
  FROM dr_scan
  GROUP BY barcode
) m

    or a CTE if you’re using MySQL 8+

    WITH m AS (
  SELECT barcode, MAX(scan_freq)
  FROM dr_scan
  GROUP BY barcode
)
SELECT COUNT(*) AS row_count
FROM m

    Or you can get all the data and the total row count at the same time using window functions in MySQL 8+

    SELECT barcode, MAX(scan_freq), COUNT(*) OVER () AS row_count
FROM dr_scan
GROUP BY barcode

    Demo on dbfiddle.uk

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search