Results 1 to 15 of 15
  1. #1
    Join Date
    Jul 2012
    Posts
    6

    Unanswered: Problem with SQL-Count

    Hi guys,
    Can you help me with these SQL-Query??

    I tried 2 different versions, both ended up with the same error-message.


    Version#1:

    SELECT DISTINCT ED1.CLIENT_NO, ED1.CLIENT_NAME, COUNT(*) AS newCount,
    (SELECT DISTINCT CLIENT_NO, CLIENT_NAME, COUNT(*) preaCount
    FROM XX.ED ED2
    HAVING
    (
    (ED2.CREATED > TO_DATE('2011-10-01', 'YYYY-MM-DD'))
    AND
    (ED2.CREATED < TO_DATE('2011-12-31', 'YYYY-MM-DD'))
    AND
    ED2.CLIENT_NO = 10 AND ED2.CLIENT_NAME = ED1.CLIENT_NAME
    )
    GROUP BY ED2.CLIENT_NO, ED2.CLIENT_NAME
    ) FROM XX.ED ED1
    WHERE (ED1.CREATED > TO_DATE('2012-01-01', 'YYYY-MM-DD'))



    Version#2;

    SELECT DISTINCT CLIENT_NO, CLIENT_NAME, COUNT(*) OVER (PARTITION BY CLIENT_NO) newCount,
    ( SELECT DISTINCT CLIENT_NO, CLIENT_NAME, COUNT(*) OVER (PARTITION BY CLIENT_NO) preCount
    FROM XX.ED ED2
    WHERE
    (ED2.CREATED > TO_DATE('2011-01-01', 'YYYY-MM-DD'))
    AND
    (ED2.CREATED < TO_DATE('2011-12-31', 'YYYY-MM-DD'))
    AND ED2.CLIENT_NO = ED1.CLIENT_NO
    ) FROM XX.ED ED1
    WHERE
    (ED1.CREATED > TO_DATE('2011-01-01', 'YYYY-MM-DD'))
    AND
    (ED1.CREATED < TO_DATE('2011-12-31', 'YYYY-MM-DD'))



    Error message in both examples: ORA-00913 Too many values (or something)... i Guess the error is in the inner-select statement, but i'm not sure.

    What is happening is that i try to list the count from the last year and from this year..

    2:nd question: Which o the above versions is to prefer?

    Anyone?

  2. #2
    Join Date
    Jul 2012
    Posts
    6
    I also tried the above with a simple

    Selec count(*)..... FROM yyyy join (select count(*) FROM xxxx Group by....),

    but couldnt get the right result that way either...

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    We don't have your tables.
    We don't have your data.
    We don't know what the expected/desired results should be.

    >Which o the above versions is to prefer?
    use the SQL that gives correct result set while consuming fewest resources.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  4. #4
    Join Date
    Jul 2012
    Posts
    6
    Quote Originally Posted by anacedent View Post
    We don't have your tables.
    We don't have your data.
    We don't know what the expected/desired results should be.

    >Which o the above versions is to prefer?
    use the SQL that gives correct result set while consuming fewest resources.
    Why do you need my tables or data? If you know how to read the SQL-statement, i'm pretty sure you can figure out what i want to do and what the outcome should be.

    But if you want me to describe:
    Count number of rows created this year, for every specific Customer number.
    Count number of rows created last year, for every specific Customer number.

    Outcome: Custumer_NO | Counts_last_year | Counts_this_year


    Not that difficult, huh?

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
      1  with y2007 as (
      2  SELECT Customer_ID, count(order_id) "2007"
      3  FROM orders
      4  WHERE order_date between to_date('2007-01-01','YYYY-MM-DD') AND TO_DATE('2007-12-31','YYYY-MM-DD')
      5  group by customer_id
      6  ),
      7  Y2006 as (
      8  SELECT Customer_ID, count(order_id) "2006"
      9  FROM orders
     10  WHERE order_date between to_date('2006-01-01','YYYY-MM-DD') AND TO_DATE('2006-12-31','YYYY-MM-DD')
     11  group by customer_id
     12  )
     13  select y2007.customer_id, y2007."2007", y2006."2006"
     14  from  y2007, y2006
     15* where y2007.customer_id = y2006.customer_id
    12:19:54 SQL> /
    
    CUSTOMER_ID	  2007	     2006
    ----------- ---------- ----------
    	144	     3		1
    	147	     3		2
    	108	     2		2
    	148	     3		1
    	102	     3		1
    	117	     2		1
    	107	     3		1
    	104	     2		1
    	103	     2		2
    
    9 rows selected.
    
    12:19:55 SQL>
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  6. #6
    Join Date
    Jul 2012
    Posts
    6
    Quote Originally Posted by anacedent View Post
    Code:
      1  with y2007 as (
      2  SELECT Customer_ID, count(order_id) "2007"
      3  FROM orders
      4  WHERE order_date between to_date('2007-01-01','YYYY-MM-DD') AND TO_DATE('2007-12-31','YYYY-MM-DD')
      5  group by customer_id
      6  ),
      7  Y2006 as (
      8  SELECT Customer_ID, count(order_id) "2006"
      9  FROM orders
     10  WHERE order_date between to_date('2006-01-01','YYYY-MM-DD') AND TO_DATE('2006-12-31','YYYY-MM-DD')
     11  group by customer_id
     12  )
     13  select y2007.customer_id, y2007."2007", y2006."2006"
     14  from  y2007, y2006
     15* where y2007.customer_id = y2006.customer_id
    12:19:54 SQL> /
    
    CUSTOMER_ID	  2007	     2006
    ----------- ---------- ----------
    	144	     3		1
    	147	     3		2
    	108	     2		2
    	148	     3		1
    	102	     3		1
    	117	     2		1
    	107	     3		1
    	104	     2		1
    	103	     2		2
    
    9 rows selected.
    
    12:19:55 SQL>
    Thanx, that works like a charm! Didn't now about the "With" statement..

    Can you tell me where i went wrong with my querys? I'm eager to learn...

  7. #7
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Just a question. That query will work if all customers are in both years, but if you have specific customers in only one year they will never show. You really need a full outer join.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  8. #8
    Join Date
    Jul 2012
    Posts
    6
    Quote Originally Posted by beilstwh View Post
    Just a question. That query will work if all customers are in both years, but if you have specific customers in only one year they will never show. You really need a full outer join.
    Very good point indeed! Didn't think of that. This query will not work for me then.

    I tried JOIN, but all i got was that "too many values" error above. Have no idea why. The 2 subquerys work fine separately, so i guess the problem is something with the "ON t1.xxxx = t2xxxx"

  9. #9
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
      1  WITH y2007
      2	  AS (SELECT customer_id,
      3		     Count(order_id) "2007"
      4	      FROM   orders
      5	      WHERE  order_date BETWEEN To_date('2007-01-01', 'YYYY-MM-DD') AND
      6					To_date('2007-12-31', 'YYYY-MM-DD')
      7	      GROUP  BY customer_id),
      8	  y2006
      9	  AS (SELECT customer_id,
     10		     Count(order_id) "2006"
     11	      FROM   orders
     12	      WHERE  order_date BETWEEN To_date('2006-01-01', 'YYYY-MM-DD') AND
     13					To_date('2006-12-31', 'YYYY-MM-DD')
     14	      GROUP  BY customer_id)
     15  SELECT Nvl(y2007.customer_id, y2006.customer_id) CUST_ID,
     16	    Nvl(y2007."2007", 0)		      YR_2007,
     17	    Nvl(y2006."2006", 0)		      YR_2006
     18  FROM   y2007
     19	    full outer join y2006
     20			 ON y2007.customer_id = y2006.customer_id
     21* ORDER  BY 1
    18:23:30 SQL> /
    
       CUST_ID    YR_2007	 YR_2006
    ---------- ---------- ----------
           101	    2	       0
           102	    3	       1
           103	    2	       2
           104	    2	       1
           105	    2	       0
           106	    0	       1
           107	    3	       1
           108	    2	       2
           109	    3	       0
           116	    4	       0
           117	    2	       1
    
       CUST_ID    YR_2007	 YR_2006
    ---------- ---------- ----------
           118	    2	       0
           119	    1	       0
           122	    1	       0
           123	    1	       0
           141	    1	       0
           142	    1	       0
           143	    1	       0
           144	    3	       1
           145	    4	       0
           146	    4	       0
           147	    3	       2
    
       CUST_ID    YR_2007	 YR_2006
    ---------- ---------- ----------
           148	    3	       1
           149	    4	       0
           150	    1	       0
           152	    1	       0
           153	    0	       1
           154	    1	       0
           156	    0	       1
           157	    1	       0
           158	    1	       0
           159	    1	       0
           160	    1	       0
    
       CUST_ID    YR_2007	 YR_2006
    ---------- ---------- ----------
           161	    1	       0
           162	    1	       0
           163	    1	       0
           164	    1	       0
           165	    1	       0
           166	    1	       0
           167	    1	       0
           169	    1	       0
           170	    0	       1
    
    42 rows selected.
    
    18:23:32 SQL>
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  10. #10
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    How about this?

    Code:
    SELECT customer_id
         , COUNT(CASE
                 WHEN order_date BETWEEN TO_DATE('2006-01-01','YYYY-MM-DD')
                                     AND TO_DATE('2006-12-31','YYYY-MM-DD') THEN
                      order_id
                 END
                ) "2006"
         , COUNT(CASE
                 WHEN order_date BETWEEN TO_DATE('2007-01-01','YYYY-MM-DD')
                                     AND TO_DATE('2007-12-31','YYYY-MM-DD') THEN
                      order_id
                 END
                ) "2007"
     FROM  orders
     GROUP BY
           customer_id

  11. #11
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    different results! This query returns users who have ZERO count for both years.
    I am using the ORDERS table as delivered by Oracle Corp with the OE sample schema
    Code:
      1  SELECT customer_id
      2	  , COUNT(CASE
      3		  WHEN order_date BETWEEN TO_DATE('2006-01-01','YYYY-MM-DD')
      4				      AND TO_DATE('2006-12-31','YYYY-MM-DD') THEN
      5		       order_id
      6		  END
      7		 ) "2006"
      8	  , COUNT(CASE
      9		  WHEN order_date BETWEEN TO_DATE('2007-01-01','YYYY-MM-DD')
     10				      AND TO_DATE('2007-12-31','YYYY-MM-DD') THEN
     11		       order_id
     12		  END
     13		 ) "2007"
     14   FROM  orders
     15   GROUP BY
     16*	    customer_id
    20:18:45  17  /
    
    CUSTOMER_ID	  2006	     2007
    ----------- ---------- ----------
    	101	     0		2
    	102	     1		3
    	103	     2		2
    	104	     1		2
    	105	     0		2
    	106	     1		0
    	107	     1		3
    	108	     2		2
    	109	     0		3
    	116	     0		4
    	117	     1		2
    
    CUSTOMER_ID	  2006	     2007
    ----------- ---------- ----------
    	118	     0		2
    	119	     0		1
    	120	     0		0
    	121	     0		0
    	122	     0		1
    	123	     0		1
    	141	     0		1
    	142	     0		1
    	143	     0		1
    	144	     1		3
    	145	     0		4
    
    CUSTOMER_ID	  2006	     2007
    ----------- ---------- ----------
    	146	     0		4
    	147	     2		3
    	148	     1		3
    	149	     0		4
    	150	     0		1
    	151	     0		0
    	152	     0		1
    	153	     1		0
    	154	     0		1
    	155	     0		0
    	156	     1		0
    
    CUSTOMER_ID	  2006	     2007
    ----------- ---------- ----------
    	157	     0		1
    	158	     0		1
    	159	     0		1
    	160	     0		1
    	161	     0		1
    	162	     0		1
    	163	     0		1
    	164	     0		1
    	165	     0		1
    	166	     0		1
    	167	     0		1
    
    CUSTOMER_ID	  2006	     2007
    ----------- ---------- ----------
    	168	     0		0
    	169	     0		1
    	170	     1		0
    
    47 rows selected.
    
    20:18:46 SQL>
    Last edited by anacedent; 07-07-12 at 00:44.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  12. #12
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Differences were column sequence and my query include customer_id
    of which both of 2007 and 2006 were 0.

    So, how about those?
    Code:
    SELECT customer_id
         , COUNT(CASE
                 WHEN order_date BETWEEN TO_DATE('2007-01-01','YYYY-MM-DD')
                                     AND TO_DATE('2007-12-31','YYYY-MM-DD') THEN
                      order_id
                 END
                ) "2007"
         , COUNT(CASE
                 WHEN order_date BETWEEN TO_DATE('2006-01-01','YYYY-MM-DD')
                                     AND TO_DATE('2006-12-31','YYYY-MM-DD') THEN
                      order_id
                 END
                ) "2006"
     FROM  orders
     WHERE order_date BETWEEN TO_DATE('2006-01-01','YYYY-MM-DD')
                          AND TO_DATE('2007-12-31','YYYY-MM-DD')
     GROUP BY
           customer_id
    OR

    Code:
    SELECT *
     FROM (SELECT customer_id
                , COUNT(CASE
                        WHEN order_date BETWEEN TO_DATE('2007-01-01','YYYY-MM-DD')
                                            AND TO_DATE('2007-12-31','YYYY-MM-DD') THEN
                             order_id
                        END
                       ) "2007"
                , COUNT(CASE
                        WHEN order_date BETWEEN TO_DATE('2006-01-01','YYYY-MM-DD')
                                            AND TO_DATE('2006-12-31','YYYY-MM-DD') THEN
                             order_id
                        END
                       ) "2006"
            FROM  orders
            GROUP BY
                  customer_id
          )
     WHERE "2007" > 0
       OR  "2006" > 0
    /* or
     WHERE "2007" + "2006" > 0
    */
    Last edited by tonkuma; 07-07-12 at 00:44. Reason: Add alernaive WHERE "2007" + "2006" > 0

  13. #13
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    I was updating my previous post with same details as you were posting your latest.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  14. #14
    Join Date
    Jul 2012
    Posts
    6
    Thanx for all the great solutions... i will try them out soon.

    But still, i want to know where i did go wrong in my initial queries.

    Why can't i use Over-Partition or a simple Join-query without getting the error-message stating i have "too many values"??

    It's good for me, and for others trying to do the same thing and reading this to know what not to do and why...

  15. #15
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by hobstructor View Post
    Why can't i use Over-Partition or a simple Join-query without getting the error-message stating i have "too many values"??
    Huh? I have no idea what you mean with "Over-Partition" and "simple Join-query", but I see only scalar subquery expression in your code; in both queries it starts on the second line and hopefully ends somewhere (hard to tell where because of no indentation).
    It (scalar subquery expression) is limited to one column and (at most) one row.
    http://docs.oracle.com/cd/E11882_01/...3.htm#i1033549
    Code:
    SQL> select (select 1 c1, 2 c2 from dual) from dual;
    select (select 1 c1, 2 c2 from dual) from dual
            *
    ERROR at line 1:
    ORA-00913: too many values
    I have no idea what is it supposed to represent, so I would not make any attempts of "fixing" it and use anacedent's/tonkuma's code instead.

Posting Permissions

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