Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2008
    Location
    Netherlands
    Posts
    58

    Unanswered: Average in query

    Is it possible to calculate an avarage based on the cusomer_id (off_klant_id) I want the avarege of all the entrys of an customer and number of orders.

    Code:
    $sql = "SELECT
    			off.offerte AS off_offerte,
    			off.klant_id AS off_klant_id,
    			off.ref_klant AS off_ref_klant,
    			off.datum_dossier AS off_datum_dossier,
    			off.datum_offerte AS off_datum_offerte,
    			off.bedrag AS off_bedrag,
    			off.plasma AS off_plasma,
    			off.water AS off_water,
    			off.laser AS off_laser,
    			off.knip_zet AS off_knip_zet,
    			off.handel AS off_handel,
    			off.dikte AS off_dikte,
    			off.materiaal AS off_materiaal,
    			off.omschrijving AS off_omschrijving,
    			off.status_id AS off_status_id,
    			off.reden_vervallen AS off_reden_vervallen,
    			off.datum_vervallen AS off_datum_vervallen,
    			off.archief_doos AS archief_doos,
    			adr.klantnaam AS adr_klantnaam,
    			ordnr.ordernummer AS ordnr_ordernummer,
    			ordnr.datum_order AS ordnr_datum_order,
    			ordnr.datum_leveren AS ordnr_datum_leveren,
    			fac.factuurnummer AS fac_factuurnummer,
    			fac.datum_verzonden AS fac_datum_verzonden,
    			fac.datum_betaald AS fac_datum_betaald,
    			st.id AS st_id,
    			st.status AS st_status
                FROM offertenummers AS off
                LEFT OUTER JOIN adressen AS adr 
                ON adr.id = off.klant_id 
                LEFT OUTER JOIN ordernummers AS ordnr
                ON ordnr.offerte_id = off.offerte 
                LEFT OUTER JOIN factuurnummers AS fac
                ON fac.offerte_id = off.offerte
                LEFT OUTER JOIN status AS st
                ON st.id = off.status_id
                ORDER BY off.offerte DESC
        		LIMIT $currentRecord, $recordsPerPage";

  2. #2
    Join Date
    Dec 2008
    Location
    Netherlands
    Posts
    58
    I made this query, does it look ok?

    Code:
    // Query voor scoringspercentage alles van klant
    	    $sql_totaal = "SELECT * FROM offertenummers WHERE klant_id = '".$row['off_klant_id']."' ";
    	    
            // Query voor scoringspercentage order van klant
    		$sql_score = "SELECT
    			off.klant_id AS off_klant_id,
    			ordnr.ordernummer AS ordnr_ordernummer
                FROM offertenummers AS off
                RIGHT OUTER JOIN ordernummers AS ordnr
                ON ordnr.offerte_id = off.offerte 
                WHERE off.klant_id = '".$row['off_klant_id']."' "; 
                
               $res_totaal = mysql_query($sql_totaal,$con);    
        	   $totaal = mysql_num_rows($res_totaal);
        	   $res_score = mysql_query($sql_score,$con);    
        	   $score = mysql_num_rows($res_score);
        	   $percentage = $score / $totaal * 100;

  3. #3
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    I made this query, does it look ok?
    This might sound a bit radical but have you tried running it? and did it produce the right results? It might be worth looking up the AVG function.

  4. #4
    Join Date
    Dec 2008
    Location
    Netherlands
    Posts
    58
    Yes, I'm running it without any problems. I just need to to know if this was the right way.

    Thank you for you're tip about 'average'.

  5. #5
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    I'm afraid your method is the wrong way. You should be using the AVG function allong with GROUP BY. It's very simple to use and if you read the link I gave then you'll quickly become an expert.

Posting Permissions

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