Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2004
    Posts
    1

    Unanswered: Need help with query

    Hello,

    What am I doing wrong here :

    I need 2 separate columns, col3 and col4, to give me a count of the records between different specified dates.

    create table abc (col1 nvarchar(30), col2 nvarchar(30), col3 int, col4 int, datee datetime)

    insert into abc values('a1','a2' , 2, 4, '2004-05-20 00:00:00')
    insert into abc values('a1','a2' , 12, 4, '2004-05-20 00:00:00')
    insert into abc values('b1','b2' , 3 , 1, '2004-05-21 00:00:00')
    insert into abc values('c1','c2' , 3, 2, '2004-05-22 00:00:00')
    insert into abc values('d1','d2' , 1 , 2, '2004-05-22 00:00:00')
    insert into abc values('e1','e2' , 4 , 3, '2004-05-21 00:00:00')

    SELECT col1, col2, col3 = count(*) , col4 from abc
    where col4 IN (select col4 =count(*)from abc
    where datee IN ( '2004-05-21 00:00:00', '2004-05-22 00:00:00')
    GROUP BY col1,col2, col4)
    AND datee IN ( '2004-05-20 00:00:00','2004-05-21 00:00:00')
    GROUP BY col1,col2, col4
    ORDER BY col3, col4 DESC

    Output :

    col1 col2 col3 col4
    b1 b2 1 1

    I know its something silly!!
    Thanks in advance!

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    What you are doing makes no sense to me. I'm sure it would help if you labeled your columns something more explanatory. What the heck is col3 supposed to represent? Why are you setting it to a count of the number of records in a restricted set? What the heck is col4? Why are you checking to see whether it matches the count of a record subset? Why are you using subquerys when you should be using joins?

    Your subquery:
    select col4 = count(*)
    from abc
    where datee IN ('2004-05-21 00:00:00', '2004-05-22 00:00:00')
    GROUP BY col1,
    col2,
    col4

    ...is confused. The select set is designed to return a scalar value, but the GROUP BY will yield a mult-record result set. You might want to start by reading up on the GROUP BY clause and how they are used in aggregate queries.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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