MySQL- Join based on column data

Question

I have a table which has data in the following format:

+---------------------+--------------+-------------------+-------------------+
| date                | downloadtime | clientcountrycode | clientcountryname |
+---------------------+--------------+-------------------+-------------------+
| 2013-07-10 10:44:29 |            2 | USA               | United States     |
| 2013-07-10 10:44:25 |            4 | USA               | United States     |
| 2013-07-10 10:44:21 |            7 | USA               | United States     |
| 2013-07-10 10:44:16 |            2 | USA               | United States     |
| 2013-07-10 10:44:10 |            3 | USA               | United States     |
+---------------------+--------------+-------------------+-------------------+

I need to prepare a csv file by querying this table. The csv file should be of the following format:

clientcountryname,clientcountrycode,2013-07-05,2013-07-06,2013-07-8...
United States,USA,22,23,24

SO, basically I need to get the average downloadtime for each country for each day. I have a query which will give me avg(downloadtime) for a particular day:

SELECT clientcountryname,clientcountrycode, avg(downloadtime), FROM tb_npp where date(date) = '2013-07-10' group by clientcountrycode;

+---------------------------------------+-------------------+-------------------+
| clientcountryname                     | clientcountrycode | avg(downloadtime) |
+---------------------------------------+-------------------+-------------------+
| Anonymous Proxy                       | A1                |          118.0833 |
| Satellite Provider                    | A2                |          978.5000 |
| Aruba                                 | ABW               |           31.8462 |

My question is: Is there a way in SQL to group the column names based on date which is present in my database?

Matt Richardson · Accepted Answer · 2013-07-10 21:48:51Z

1

If I understand you question correctly, you should just be able to group by the date as well:

SELECT clientcountryname,clientcountrycode,Date, avg(downloadtime),
FROM tb_npp 
GROUP BY clientcountrycode,clientCountryCode,Date;

answered Jul 10, 2013 at 21:48

Matt Richardson

563 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

wanab_geek Over a year ago

THanks, but no I was looking at doing a full outer join based on data in the date column

Collectives™ on Stack Overflow

MySQL- Join based on column data

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related