...

View Full Version : Combine to one SQL query?



DJCMBear
10-17-2011, 01:44 PM
Hello

I am trying to combine two queries to one query, here is the query I have tried but it doesn't seem to work how I want it to, How can I find all unique date values and then form that as it's own query collection to use on the second query and with that be able to obtain an array of how many times a date shows up in the table.

SQL Query:


SELECT COUNT(*)
FROM (SELECT `Date`
FROM `orders`
WHERE `Date` != ''
GROUP BY `Date`
ORDER BY `Date`) As `Collection`
WHERE `Collection`.`Date` = `Date`


The way I want it to work is like I said before, to look for all unique dates then look at the table again using the unique dates found before to calculate how many times each of them show up in the table, once that has run what I want returned is an array listing the unique dates and how many times that date appears in the table so something like this.

The type of array I wanted to be returned:


Array (
[0] => Array (
[Date] => '17-10-2011'
[Count] => 5
)
[1] => Array (
[Date] => '01-10-2012'
[Count] => 1
)
[2] => Array (
[Date] => '12-09-2012'
[Count] => 10
)
)


Please let me know if this can be done if so then how.

Thank you
- DJCMBear

EDIT:
I think I have done it however the query runs very slowly, this is what I currently have.


SELECT DISTINCT(`Outer`.`Date`),
(SELECT COUNT(*)
FROM `orders`
WHERE `Date` != '' && `Date` = `Outer`.`Date`) As `Count`
FROM `orders` As `Outer`
WHERE `Outer`.`Date` != ''

BubikolRamios
10-17-2011, 06:08 PM
why this would not work:



SELECT date ,COUNT(date) As Count
FROM orders
WHERE Date <> ''
group by date

Old Pedant
10-17-2011, 07:26 PM
Actually, if your date field is a DATETIME or TIMESTAMP column, then it can *NEVER* be '' (a blank string), so you probably need to check for NULL instead.


SELECT `date` ,COUNT(*) As `Count`
FROM orders
WHERE `date` IS NOT NULL
GROUP BY `date`

The word DATE is a keyword in MySQL and really shouldn't be used as a field name, but if you do use it, you should probably put `...` around it to protect against misinterpretation.

DJCMBear
10-18-2011, 09:17 AM
You both have just saved my life lol I went with this solution in the end.



SELECT `Date`, COUNT(`Date`) AS `Count`
FROM `orders`
WHERE `Date` <> '' && `Date` > :DATE
GROUP BY `Date`
ORDER BY `Date`


Thank you for both of your help :thumbsup:



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum