Search code examples
mysqlselectmaxminrecord-count

How to get number of records along with min/max values in MySQL?


I have a search form (running MySQL 5.0.88) to drill down through a product table. The search is a 2-step search. First run gets the number of results, 2nd run gets the data of records x-y.

I'm displaying min/max prices across all records by default and now want to update the min/max values based on the query results. I was thinking the best way to do this is inside the inital query, that gets the number of records.

Currently it looks like this:

SELECT  COUNT( a.id ) AS recordcount
        , a.nos
        , a.nos_anzeige

FROM artikelstammdaten a

WHERE 
        a.iln != "1111111111111" AND a.iln != "2222222222222" AND a.iln != "7777777777777"
    AND a.aktiv = "ja"
    AND a.artikelnummer LIKE '%name%' 
AND (a.modus = "OPEN" 
        OR a.iln IN ( 55555555555,66666666666,2222222222222 ) 
        )

GROUP BY a.iln, a.artikelnummer, a.preis_aktuell, a.artikelbezeichnung
HAVING (( sum(a.bestand) != 0 ) OR (a.nos = "ja" AND a.anzeige  = "ja" ))

Which gives me all matching records.

Problem:
Actually I only want the number of records (~ query.recordcount ) in a single record and not the individual records one by one (my attempt with COUNT doesn't work). Something like this:

  totalrecords    min-price   max-price
  12345           9.99        1.204

where I can try to select min/max values across the resultset.

Question:
How do I have to modify my query, so I'm only getting the totalrecords (and can try to add min/max values)?

THANKS!

** EDIT: **
My table looks like this:

 CREATE TABLE dummy (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`iln` VARCHAR(13) NULL DEFAULT NULL,
`ean` VARCHAR(35) NULL DEFAULT NULL,
`artikelnummer` VARCHAR(35) NULL DEFAULT NULL,
`preis_ek` DECIMAL(12,2) NULL DEFAULT NULL,
`preis_vk` DECIMAL(12,2) NULL DEFAULT NULL,
`firma` VARCHAR(35) NULL DEFAULT NULL,
`nos` VARCHAR(4) NULL DEFAULT NULL,
`nos_anzeige` VARCHAR(4) NULL DEFAULT NULL,
`aktiv` VARCHAR(4) NULL DEFAULT NULL,
`modus` VARCHAR(4) NULL DEFAULT NULL,
`bestand` DECIMAL(10,0) NULL DEFAULT '0'
 )  

Prices would be preis_ek and preis_vk where I'm looking for min/max for each. THANKS!

EDIT: Running the query like it is gives me a resultset like this:

"id"    "nos"   "nos_anzeige"   "MIN(a.preis_ek)"
"1153837"   "nein"  "nein"  "25,10"
"1153797"   "nein"  "nein"  "12,40"
....

which picks the min per record not across the resultset, if I'm not mistaken


Solution

  • You can use

        SELECT count(recordcount), 
           Max(max_price), 
           Min(min_price) 
           FROM   (SELECT Count(a.id)   AS recordcount, 
                   a.nos, 
                   a.nos_anzeige, 
                   Max(preis_ek) AS max_price, 
                   Min(preis_ek) AS min_price 
            FROM   artikelstammdaten a 
            WHERE  a.iln != "1111111111111" 
                   AND a.iln != "2222222222222" 
                   AND a.iln != "7777777777777" 
                   AND a.aktiv = "ja" 
                   AND a.artikelnummer LIKE '%name%' 
                   AND ( a.modus = "open" 
                          OR a.iln IN ( 55555555555, 66666666666, 2222222222222 ) ) 
            GROUP  BY a.iln, 
                      a.artikelnummer, 
                      a.preis_aktuell, 
                      a.artikelbezeichnung 
            HAVING ( ( Sum(a.bestand) != 0 ) 
                      OR ( a.nos = "ja" 
                           AND a.anzeige = "ja" ) )) temp