Tom's Guide | Tom's Hardware | Tom's Games
 |
 |
 |
I'm basically trying to do a count within a count, or a subquery. The below query will show me the transaction numbers that appear one or more times between a set of dates for each store. I'm using Informix SQL.
SELECT store,
COUNT(transaction_number) AS NumOccurrences
FROM sales
WHERE date between '06-20-2007' AND '07/31/2007'
GROUP BY store
HAVING ( COUNT(transaction_number) >= 1 )The results will look like this
store   numoccurrances
1Â Â Â Â Â Â Â Â Â Â 3
1Â Â Â Â Â Â Â Â Â Â 1
1Â Â Â Â Â Â Â Â Â Â 2
5Â Â Â Â Â Â Â Â Â Â 1
5Â Â Â Â Â Â Â Â Â Â 1
5Â Â Â Â Â Â Â Â Â Â 2What I want to do next, and am having trouble with, is to subquery how many times each store appears after the above query is complete. It will look like this because stores 1 and 5 each appeared 3 times from the first query.
store   numoccurrances
1Â Â Â Â Â Â Â Â Â Â 3
5Â Â Â Â Â Â Â Â Â Â 3
Without having used Informix SQL before, I'd assume it'd go something like this:
SELECT store, COUNT(NumOccurrences)
FROM (SELECT store, COUNT(transaction_number) AS NumOccurrences
FROM sales
WHERE date between '06-20-2007' AND '07/31/2007'
GROUP BY store
HAVING ( COUNT(transaction_number) >= 1 )
)
GROUP BY store
0 
Hey Razor2.3,
Thanks for the reply!
I understand what your doing. I modified my query a little. The inner query beginning with the second SELECT function, works fine by itself.
SELECT store,
COUNT(store) AS Total
FROM (SELECT store, transaction_number,
      COUNT(transaction_number) AS NumOccurrences
      FROM sales
      WHERE date between '06-20-2007' AND '07/31/2007'
      GROUP BY store, transaction_number
      HAVING ( COUNT(transaction_number) >= 1 )
     )
GROUP BY storeWhen I make it a subquery, it doesn't work at all. I receive the following error:
"37000(-201)[Informix][Informix ODBC Driver][Informix]A syntax error has occurred"
The -201 code means that there is missing or extra punctuation, or that something is mispelled, but I'm not seeing it and am still plugging away at it.
0
I think I found the problem. The syntax I'm using is for Informix Dynamic Server (IDS) version 10. I believe I'm using verion 9.3. I'll have to locate the SQL syntax for IDS 9.3.
0  |
 |
 |
This post is quite old and has been locked from receiving new replies. Please create a new posting instead.
| Ads by Google |
