Help with a query

[vioravis]

I have two tables Table A and Table B as follows:

 

Table A

Day

1

2

3

4

 

Table B

DayCount

2

1

2

3

4

I want to create a resulting table (Table C) such a way that Table contains the number of times the value in Table B exceeds each row in Table A.

 

Table C

Day  Count

1          5

2          4

3          3

4          2

 

The first column of Table C is from Table A. The second column contains how many in Table B exceeds the column Day. For example, 5 days have value greater than 1 (in Table B). 4 days have value greater than 2 in Table B and so on.

 

Can someone please help me with the appropriate query??

 

Thank you.

[fenway]

I assume you mean "greater than or equal to", based on a count of 5 for day 1, not 4.

 

select a.day, sum( if( b.daycount >= a.day, 1, 0 ) ) AS cnt
from tableA AS a
cross join tableB AS b