www.webdeveloper.com
Results 1 to 4 of 4

Thread: 2 Different Results - 1 Query

  1. #1
    Join Date
    Jul 2009
    Posts
    138

    Exclamation 2 Different Results - 1 Query

    Hello to all,

    I am just trying to get 2 differnet results from MySQL DB using 1 query, but I have got this error message:

    #1241 - Operand should contain 1 column(s)

    Any one know where I am making error, please see below:

    SELECT
    (SELECT MONTH(CurrentDate), YEAR(CurrentDate), COUNT(*),

    SUM(IF(idFranchise = '1', 1,0)) AS `Nicky`,
    SUM(IF(idFranchise = '2', 1,0)) AS `Alice`,
    SUM(IF(idFranchise = '3', 1,0)) AS `Sam`,
    SUM(IF(idFranchise = '4', 1,0)) AS `Beauty`,
    SUM(IF(idFranchise = '5', 1,0)) AS `Danielle`,
    SUM(IF(idFranchise = '6', 1,0)) AS `Stephanie`,
    SUM(IF(idFranchise = '7', 1,0)) AS `Jenny`,
    SUM(IF(idFranchise = '8', 1,0)) AS `Marie`,
    SUM(IF(idFranchise = '9', 1,0)) AS `Kim`,
    SUM(IF(idFranchise = '10', 1,0)) AS `Dannique`,

    COUNT(idFranchise) AS `total`

    FROM appointment
    WHERE YEAR(CurrentDate) = '$period_year'
    GROUP BY YEAR(CurrentDate), MONTH(CurrentDate)

    )
    as total_rows,
    MONTH(CurrentDate), YEAR(CurrentDate), COUNT(idFranchise) AS `total` FROM appointment WHERE YEAR(CurrentDate) = '$period_year' AND css = 'confirmed';

  2. #2
    Join Date
    Jul 2010
    Location
    /ramdisk/
    Posts
    865
    You are getting that because you're attempting to select multiple columns in a subquery.

    I wont swear to it, but you're limited to 1 column AND 1 row as the result of your subquery.

    You are trying to get this:
    Code:
    month, year, count(*), count(idFranchise)
    +------+-------+-------+-------+
    |  1   | 2011  | 99999 | 55555 |
    +------+-------+-------+-------+
    |  2   | 2011  | 99999 | 44444 |
    +------+-------+-------+-------+
    But you are limited to this:
    Code:
    subquery_result
    +------------------+
    |  one cell only   |
    +------------------+


    It may just be a typo on your part.

    There are ways you can select all of those columns using subqueries. But it will be 1 column per subquery.

    There is a method to the madness. I can see you only need 1 subquery (at most), if that. The subquery would be on COUNT(*), and you may not even want to be selecting that, but actually using it for algebra inside of your query. You can pull values from your normal query into your subquery (no point in selecting from the same table twice if you dont have to )

    Here is a short example:

    SELECT 'foo' as baz,
    (
    SELECT CONCAT(baz, 'bar') as cantThinkOfAnotherName
    )
    Last edited by eval(BadCode); 02-12-2011 at 07:04 AM.

  3. #3
    Join Date
    Jan 2003
    Location
    Dundee, Scotland
    Posts
    1,367
    Try wrap your sub queries in brackets "()"

    Code:
    SELECT 
    (SELECT MONTH(CurrentDate), YEAR(CurrentDate), COUNT(*), 
    
    (SUM(IF(idFranchise = '1', 1,0)) AS `Nicky`), 
    (SUM(IF(idFranchise = '2', 1,0)) AS `Alice`),  etc..
    regards

    Ribs
    ----------------------------------

  4. #4
    Join Date
    Jul 2010
    Location
    /ramdisk/
    Posts
    865
    There are ways you can select all of those columns using subqueries. But it will be 1 column per subquery.

    I thought I would correct myself here. You can select multiple columns if you are using a "temp table". So instead of writing your query into the select clause, you write it into the from clause

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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



Recent Articles