Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    Senior Coder DJCMBear's Avatar
    Join Date
    Mar 2010
    Location
    United Kindom
    Posts
    1,173
    Thanks
    14
    Thanked 136 Times in 136 Posts

    Combine to one SQL query?

    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:
    Code:
    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:
    Code:
    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.
    Code:
    SELECT DISTINCT(`Outer`.`Date`),
           (SELECT COUNT(*)
            FROM `orders`
            WHERE `Date` != '' && `Date` = `Outer`.`Date`) As `Count`
    FROM `orders` As `Outer`
    WHERE `Outer`.`Date` != ''
    Last edited by DJCMBear; 10-17-2011 at 02:10 PM.
    Official BinPress hand picked coder.
    For anyone worried about SQL injection go have a look at my small yet powerful script here.
    Go Pledge for Light Table, if it hit's $300,000 Python and other languages will get added.
    I am 1 of 65,608 people to get a Pebble Watch :P

  • #2
    Senior Coder
    Join Date
    Dec 2005
    Location
    Slovenia
    Posts
    1,960
    Thanks
    120
    Thanked 76 Times in 76 Posts
    why this would not work:

    Code:
    SELECT date ,COUNT(date) As Count
            FROM orders
            WHERE Date <> '' 
            group by date
    Found a flower or bug and don't know what it is ?
    agrozoo.net galery
    if you don't spot search button at once, there is search form:
    agrozoo.net galery search

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,210
    Thanks
    75
    Thanked 4,343 Times in 4,309 Posts
    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.
    Code:
    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.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #4
    Senior Coder DJCMBear's Avatar
    Join Date
    Mar 2010
    Location
    United Kindom
    Posts
    1,173
    Thanks
    14
    Thanked 136 Times in 136 Posts
    You both have just saved my life lol I went with this solution in the end.

    Code:
    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
    Official BinPress hand picked coder.
    For anyone worried about SQL injection go have a look at my small yet powerful script here.
    Go Pledge for Light Table, if it hit's $300,000 Python and other languages will get added.
    I am 1 of 65,608 people to get a Pebble Watch :P


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •