CODEWITHSUNDEEP
mysql
Tim Tim
Tim Tim

Reputation: 9

How can I make these two queries into one?

I have two tables, one for downloads and one for uploads. They are almost identical but with some other columns that differs them. I want to generate a list of stats for each date for each item in the table.

I use these two queries but have to merge the data in php after running them. I would like to instead run them in a single query, where it would return the columns from both queries in each row grouped by the date. Sometimes there isn't any download data, only upload data, and in all my previous tries it skipped the row if it couldn't find log data from both rows.

How do I merge these two queries into one, where it would display data even if it's just available in one of the tables?

SELECT DATE(upload_date_added) as upload_date, SUM(upload_size) as upload_traffic, SUM(upload_files) as upload_files
FROM packages_uploads
WHERE upload_date_added BETWEEN '2011-10-26' AND '2011-11-16'
GROUP BY upload_date
ORDER BY upload_date DESC


SELECT DATE(download_date_added) as download_date, SUM(download_size) as download_traffic, SUM(download_files) as download_files
FROM packages_downloads
WHERE download_date_added BETWEEN '2011-10-26' AND '2011-11-16'
GROUP BY download_date
ORDER BY download_date DESC

I want to get result rows like this:

date, upload_traffic, upload_files, download_traffic, download_files

All help appreciated!

Upvotes: 0

Views: 81

Answers (4)

Blasanka
Blasanka

Reputation: 22437

There is number of ways that you can do this. You can join using primary key and foreign key. In case if you do not have relationship between tables,

You can use,

  • LEFT JOIN / LEFT OUTER JOIN

Returns all records from the left table and the matched records from the right table. The result is NULL from the right side when there is no match.

  • RIGHT JOIN / RIGHT OUTER JOIN

Returns all records from the right table and the matched records from the left table. The result is NULL from the left side when there is no match.

  • FULL OUTER JOIN

Return all records when there is a match in either left or right table records.

  • UNION

Is used to combine the result-set of two or more SELECT statements.

Each SELECT statement within UNION must have the same number of, columns The columns must also have similar data types The columns in, each SELECT statement must also be in the same order.

  • INNER JOIN

Select records that have matching values in both tables. -this is good for your situation.

  • INTERSECT

Does not support MySQL.

  • NATURAL JOIN

All the column names should be matched.

Since you dont need to update these you can create a view from joining tables then you can use less query in your PHP. But views cannot update. And you did not mentioned about relationship between tables. Because of that I have to go with the UNION.

Like this,

CREATE VIEW checkStatus
AS
SELECT 
    DATE(upload_date_added) as upload_date,
    SUM(upload_size) as upload_traffic,
    SUM(upload_files) as upload_files
FROM packages_uploads
WHERE upload_date_added BETWEEN '2011-10-26' AND '2011-11-16'
GROUP BY upload_date
ORDER BY upload_date DESC

UNION

SELECT 
    DATE(download_date_added) as download_date,
    SUM(download_size) as download_traffic,
    SUM(download_files) as download_files
FROM packages_downloads
WHERE download_date_added BETWEEN '2011-10-26' AND '2011-11-16'
GROUP BY download_date
ORDER BY download_date DESC

Then anywhere you want to select you just need one line:

SELECT * FROM checkStatus

learn more.

Upvotes: 0

Robbo_UK
Robbo_UK

Reputation: 12129

Without knowing the schema is hard to give the exact answer so please see the following as a concept not a direct answer.

You could try left join, im not sure if the table package exists but the following may be food for thought

SELECT 
  p.id,  
  up.date as upload_date
  dwn.date as download_date
FROM 
package p
LEFT JOIN package_uploads up ON 
    ( up.package_id = p.id WHERE up.upload_date = 'etc' )
LEFT JOIN package_downloads dwn ON 
    ( dwn.package_id = p.id WHERE up.upload_date = 'etc' )

The above will select all the packages and attempt to join and where the value does not join it will return null.

Upvotes: 0

RolandoMySQLDBA
RolandoMySQLDBA

Reputation: 44343

Your two queries can be executed and then combined with the UNION cluase along with an extra field to identify Uploads and Downloads on separate lines:

SELECT
    'Uploads' TransmissionType,
    DATE(upload_date_added) as TransmissionDate,
    SUM(upload_size) as TransmissionTraffic,
    SUM(upload_files) as TransmittedFileCount
FROM
    packages_uploads 
WHERE upload_date_added BETWEEN '2011-10-26' AND '2011-11-16' 
GROUP BY upload_date 
ORDER BY upload_date DESC 
UNION 
SELECT
    'Downloads',
    DATE(download_date_added),
    SUM(download_size),
    SUM(download_files) 
FROM packages_downloads 
WHERE download_date_added BETWEEN '2011-10-26' AND '2011-11-16' 
GROUP BY download_date 
ORDER BY download_date DESC;

Give it a Try !!!

Upvotes: 1

m0skit0
m0skit0

Reputation: 25873

What you're asking can only work for rows that have the same add date for upload and download. In this case I think this SQL should work:

SELECT 
    DATE(u.upload_date_added) as date,
    SUM(u.upload_size) as upload_traffic,
    SUM(u.upload_files) as upload_files,
    SUM(d.download_size) as download_traffic,
    SUM(d.download_files) as download_files
FROM 
    packages_uploads u, packages_downloads d
WHERE u.upload_date_added = d.download_date_added
AND u.upload_date_added BETWEEN '2011-10-26' AND '2011-11-16'
GROUP BY date
ORDER BY date DESC

Upvotes: 0

Related Questions