How to Get Row Number while Multiple Union used and All (*) Data Column is not Grabbed?

Question

<?php

$result = mysql_query("SELECT COUNT(`uid`), `mac`, SUM(`time`)/60 AS ConnTime, SUM(`download`)/1000000 AS TotalDown, SUM(`upload`)/1000000 AS TotalUp
FROM `cdr_data_january`
WHERE (`towerid` LIKE '".$btsid."') AND (`ldate` BETWEEN '".$date1."' AND '".$date2."')
GROUP BY `uid`
UNION
SELECT COUNT(`uid`), `mac_add`, SUM(`time`)/60 AS ConnTime, SUM(`download`)/1000000 AS TotalDown, SUM(`upload`)/1000000 AS TotalUp
FROM `cdr_data_february`
WHERE (`bts` LIKE '".$btsid."') AND (`ldate` BETWEEN '".$date1."' AND '".$date2."')
GROUP BY `uid`");

mysql_fetch_row($result); 
$numrows = mysql_num_rows($result);

?>

edit here's the sql formatted for readability.

    SELECT COUNT(`uid`), 
           `mac`, 
           SUM(`time`)/60 AS ConnTime, 
           SUM(`download`)/1000000 AS TotalDown, SUM(`upload`)/1000000 AS TotalUp
      FROM `cdr_data_january`
     WHERE (`towerid` LIKE '".$btsid."') 
       AND (`ldate` BETWEEN '".$date1."' AND '".$date2."')
  GROUP BY `uid`
     UNION
    SELECT COUNT(`uid`), 
           `mac_add`, 
           SUM(`time`)/60 AS ConnTime, 
           SUM(`download`)/1000000 AS TotalDown, 
           SUM(`upload`)/1000000 AS TotalUp
     FROM `cdr_data_february`
    WHERE (`bts` LIKE '".$btsid."') 
      AND (`ldate` BETWEEN '".$date1."' AND '".$date2."')
 GROUP BY `uid`

The above row is not returning me Number of Row.

The above code works while I grab entire table with (*) and count it like COUNT(*).

Please give me a solution so that I can get Number of Row for such Query where multiple UNION is used, and all columns are not grabbed.

*sidenote:* stop using `mysql_*` functions, they are deprecated. Try MySQLi or PDO instead. — Raptor, Jun 24 '13 at 16:19

score 1 · Answer 1 · answered Jun 24 '13 at 16:57

It is impossible to tell from your question what result you require. A count of rows is not the same thing in SQL parlance as a row number. It also appears that you don't understand how to use COUNT and GROUP BY. This matters because your question does not describe what you want, and people who would try to help you must guess based on your query.

Your UNION will return a row for each matching combination of mac and uid for january and february.

Perhaps you want the total number of rows from your January and February tables that contribute to your report?

Try this to summarize your January data. You can use a similar query for February, but I will leave that to you.

   SELECT COUNT(*) AS recordcount,
           `uid`,
           `mac`, 
           SUM(`time`)/60 AS ConnTime, 
           SUM(`download`)/1000000 AS TotalDown, 
           SUM(`upload`)/1000000 AS TotalUp
      FROM `cdr_data_january`
     WHERE (`towerid` LIKE '".$btsid."') 
       AND (`ldate` BETWEEN '".$date1."' AND '".$date2."')
  GROUP BY `uid`, `mac`

This will give you a row in your result set for each distinct combination of uid and mac. If you also need a summary count, use this GROUP BY instead.

  GROUP BY `uid`, `mac` WITH ROLLUP

You may find it helpful to refactor your multi-month query as follows:

   SELECT COUNT(*) AS recordcount,
           `uid`,
           `mac`, 
           SUM(`time`)/60 AS ConnTime, 
           SUM(`download`)/1000000 AS TotalDown, 
           SUM(`upload`)/1000000 AS TotalUp
      FROM (
                SELECT * FROM `cdr_data_january`
                 WHERE (`towerid` LIKE '".$btsid."') 
                  AND (`ldate` BETWEEN '".$date1."' AND '".$date2."')
             UNION ALL 
                SELECT * FROM `cdr_data_february`
                 WHERE (`towerid` LIKE '".$btsid."') 
                   AND (`ldate` BETWEEN '".$date1."' AND '".$date2."')
           ) data
  GROUP BY `uid`, `mac` WITH ROLLUP

This will give you just one row for each uid/mac combination from the union of your Jan and Feb call detail records. Notice that the WHERE clauses are repeated so your query can take advantage of indexing in your crd tables if it is present.

(Commenters are correct: with this code you're begging for a cracker to break in and wreck your database using sql injection. Move to prepared statements using, perhaps, mysqli.)

please help me on this one > http://stackoverflow.com/questions/17281101/how-to-do-pagination-while-union-is-used-in-mysql > This is the exact problem in fact... — Nirjhor, Jun 24 '13 at 17:09

How to Get Row Number while Multiple Union used and All (*) Data Column is not Grabbed?

1 Answers1