Counting the number of rows in a UNION

[longdon]

Suppose I have 2 tables: products & services. They have different fields, but have the following in common:<br><br>Inv_no: integer<br>Period: char<br><br>Say I need to determine how many invoices I have for a given period. I want to do get the number of rows in the following query:<br><br>SELECT Inv_no FROM<br>products WHERE Period = 'April'<br>UNION<br>SELECT Inv_no FROM<br>services WHERE Period = 'April'<br><br>Any ideas?&nbsp;&nbsp;

 

[carp]

How about:<br><br>SELECT count(*) <br>FROM (SELECT Inv_no FROM<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;products WHERE Period = 'April'<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;UNION<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SELECT Inv_no FROM<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;services WHERE Period = 'April');

 

[longdon]

That is a good suggestion... unfortunately, Ingres II must not support subqueries. I think that this is still a limitation with MySQL as well. For interactive use, I have been just selecting on the union, waiting for the output to stop scrolling, and looking at the number of rows returned. I suppose if I wanted to do it in a program, I would have to have the rights to create tables, and use a temp table.... Thanks tho!

 

[DougP]

Will this work<br><br>SELECT COUNT(*) AS Expr1, TABLE2.Date<br>FROM TABLE2 INNER JOIN<br>&nbsp;&nbsp;&nbsp;TABLE1 ON TABLE2.Date = TABLE1.Date<br>WHERE TABLE2.Date = 'April'<br>GROUP BY TABLE2.Date<br><br>I know my fields don't match yours.<br>But I have two tables and it returns a count<br>I also changed the date to verify that it is giving me a different count <p>DougP<br><a href=mailto: dposton@universal1.com> dposton@universal1.com</a><br><a href= > </a><br> Ask me how Bar-codes can help you be more productive.