June 15, 2015 at 12:40 pm
Hello All,
I have to use a Group By in Sub query that will give me my desired results.
SELECT count(hc), CS, S_W, (SELECT COUNT(Reg_Temp)
FROM CS_S
WHERE Reg_Temp = "Reg") as [RegOnly]
FROM CS_S
GROUP BY S_W, CS
ORDER By CS, S_W
The query I have above counts ALL the Reg, I'd want it to give only the count that I'd get by using Group By clause i.e. S_W and CS.
Please Help!
Thanks
June 15, 2015 at 12:58 pm
Hi,
Try:
SELECT
count(hc),
CS,
S_W,
COUNT(case when Reg_Temp = 'Reg' then 1 end) as [RegOnly]
FROM CS_S
GROUP BY S_W, CS
ORDER By CS, S_W
Hope this helps.
June 15, 2015 at 1:07 pm
This may be better
SELECT
count(hc),
CS,
S_W,
SUM(case when Reg_Temp = 'Reg' then 1 ELSE 0 end) as [RegOnly]
FROM CS_S
GROUP BY S_W, CS
ORDER By CS, S_W
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
June 15, 2015 at 1:56 pm
I tried both of the queries posted by you guys but sadly they are just throwing an error.
So basically I'd want these two queries to be one, both of these queries run perfectly fine when I run them individually but my desired result is the combination of these both:-
SELECT count(hc), CS, S_W
FROM CS_S
GROUP BY S_W, CS
ORDER By CS, S_W
SELECT count(reg_temp), CS, S_W
FROM CS_S
WHERE reg_temp = "Reg"
GROUP BY S_W, CS
ORDER By CS, S_W
Also, I'm pulling this data from an MS Access DB on to Excel thru a macro using ADOBD connection, if this helps!
June 15, 2015 at 2:08 pm
sakmsb (6/15/2015)
I tried both of the queries posted by you guys but sadly they are just throwing an error.So basically I'd want these two queries to be one, both of these queries run perfectly fine when I run them individually but my desired result is the combination of these both:-
SELECT count(hc), CS, S_W
FROM CS_S
GROUP BY S_W, CS
ORDER By CS, S_W
SELECT count(reg_temp), CS, S_W
FROM CS_S
WHERE reg_temp = "Reg"
GROUP BY S_W, CS
ORDER By CS, S_W
Also, I'm pulling this data from an MS Access DB on to Excel thru a macro using ADOBD connection, if this helps!
The reason this doesn't work is because MS Access doesn't support quite the same set of SQL that SQL Server does. You posted in the SQL 2012 forum instead of the MS Access forum, so the responders assumed your data was in SQL Server. As you didn't disclose that rather critical difference until now, we had no way to know. You'll need to substitute an IIF function for that CASE statement. Do you know how to do that?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 15, 2015 at 3:06 pm
sgmunson (6/15/2015)
sakmsb (6/15/2015)
The reason this doesn't work is because MS Access doesn't support quite the same set of SQL that SQL Server does. You posted in the SQL 2012 forum instead of the MS Access forum, so the responders assumed your data was in SQL Server. As you didn't disclose that rather critical difference until now, we had no way to know. You'll need to substitute an IIF function for that CASE statement. Do you know how to do that?
Apologies for not mentioning it before, I'm quite new to this and I thought as this is an SQL query I posted.
Not sure about the IIF Function. Would be thankful, if you could help me or direct me to correct forum.
Thanks!
June 15, 2015 at 6:09 pm
sakmsb (6/15/2015)
sgmunson (6/15/2015)
sakmsb (6/15/2015)
The reason this doesn't work is because MS Access doesn't support quite the same set of SQL that SQL Server does. You posted in the SQL 2012 forum instead of the MS Access forum, so the responders assumed your data was in SQL Server. As you didn't disclose that rather critical difference until now, we had no way to know. You'll need to substitute an IIF function for that CASE statement. Do you know how to do that?
Apologies for not mentioning it before, I'm quite new to this and I thought as this is an SQL query I posted.
Not sure about the IIF Function. Would be thankful, if you could help me or direct me to correct forum.
Thanks!
The function is easy to use:
IIF(condition, value if true, value if false)
Translating the CASE statement should be trivial, but you basically put the one thing you are testing into the condition portion of IIF. You can nest IIF functions as needed.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 15, 2015 at 6:12 pm
The proper forum is the MS Access forum, here:
http://www.sqlservercentral.com/Forums/Forum131-1.aspx
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 16, 2015 at 5:11 am
sgmunson (6/15/2015)
The proper forum is the MS Access forum, here:
Thanks!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply