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
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