May 11, 2010 at 2:24 am
I just don't get it. This is what I am trying to do, yet it will not let me add the Gender column because it is not included in an aggregate or any other column that falls into that category. I just cannot not remember the workaround at this point. I am brain dead on it at this point:
SELECT Gender, CMType, BreedName, COUNT(*) AS 'Nr By Gender/Type/Breed'
FROM BREED JOIN
(SELECT Gender, CMID, BreedID, COUNT(*) AS 'Nr By Gender/Type/Breed'
FROM K9
GROUP BY Gender, CMID, BreedID) AS CNTR
ON BREED.BreedID = CNTR.BreedID
JOIN CMTYPE ON CNTR.CMID = CMTYPE.CMID
ORDER BY Gender, CMType, BreedName
The subquery works perfectly. All I am trying to do is to put names to all the ID numeric types. It must be this week...
Any suggestions please?
Thanks
Dobermann
May 11, 2010 at 2:31 am
I think you are missing group by clause for the main query.
Nag
Nag
------------------------------------------------
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
May 11, 2010 at 2:37 am
So much closer, but not quite! Now it runs, but I am getting all 1s in the last column instead of the actual count, COUNT(*) AS 'Nr By Gender/Type/Breed'.
Any more great ideas?
Thanks again!
May 11, 2010 at 3:06 am
Try this query, no need of using sub query:
SELECT Gender, CMType, BreedName, COUNT(*) AS 'Nr By Gender/Type/Breed'
FROM BREED JOIN K9 AS CNTR
ON BREED.BreedID = CNTR.BreedID
JOIN CMTYPE ON CNTR.CMID = CMTYPE.CMID
GROUP BY Gender, CMType, BreedName
ORDER BY Gender, CMType, BreedName
and you will get count more than one only if you have repeating data.
Nag.
Nag
------------------------------------------------
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
May 11, 2010 at 3:28 am
Again, without seeing your BREED table and K9 table how are we to assume what data is in, where you are going wrong and what we can do for you.
Please go through this following article and helping us help you?? 🙂
CLICK HERE FOR FORUM POSTING ETIQUETTES - JEFF MODEN[/url]
When u do so, i am sure a lot of us will help u instantly...
So please post
1. Table Structures - CREATE TABLE scripts
2. Constraints or Indexes in the table, - CREATE SCRIPTS
3. SAMPLE DATA - INSERT INTO TABLE scripts
4. Desired output - some visual representation of this.
I could guess very little information from your post mate 🙁 !!
Cheers!
May 11, 2010 at 7:48 am
Dobermann (5/11/2010)
I just don't get it. This is what I am trying to do, yet it will not let me add the Gender column because it is not included in an aggregate or any other column that falls into that category. I just cannot not remember the workaround at this point. I am brain dead on it at this point:SELECT Gender, CMType, BreedName, COUNT(*) AS 'Nr By Gender/Type/Breed'
FROM BREED JOIN
(SELECT Gender, CMID, BreedID, COUNT(*) AS 'Nr By Gender/Type/Breed'
FROM K9
GROUP BY Gender, CMID, BreedID) AS CNTR
ON BREED.BreedID = CNTR.BreedID
JOIN CMTYPE ON CNTR.CMID = CMTYPE.CMID
ORDER BY Gender, CMType, BreedName
The subquery works perfectly. All I am trying to do is to put names to all the ID numeric types. It must be this week...
Any suggestions please?
Thanks
Dobermann
Without sample table declarations to test against, I can't know for sure, but it seems to me that you don't need to add another count(*) in the outer SELECT, you just need to return the column 'Nr By Gender/Type/Breed' from the inner SELECT which already contains the row count. Once done, you can JOIN with the outer SELECT all the descriptions and additional columns you need without requiring another GROUP BY.
SELECT Gender, CMType, BreedName, 'Nr By Gender/Type/Breed'
FROM BREED JOIN
(SELECT Gender, CMID, BreedID, COUNT(*) AS 'Nr By Gender/Type/Breed'
FROM K9
GROUP BY Gender, CMID, BreedID) AS CNTR
ON BREED.BreedID = CNTR.BreedID
JOIN CMTYPE ON CNTR.CMID = CMTYPE.CMID
ORDER BY Gender, CMType, BreedName
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
May 11, 2010 at 2:05 pm
After being appropriately and rightly chastised for not including all that I should have, I will strive to do things correctly next time.
Nag, your response was what I needed and works just fine. Thanks! I've been having such a bad week, I think I would even question why the sun came up in the east and not the west. :hehe:
To all who responded, thanks, and again, I will post appropriate code next time.
Cheers,
Dobermann
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply