Assistance - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Assistance

Author

Topic

zai
Starting Member

9 Posts

Posted - 2010-06-07 : 00:52:49

I have two statements that I would like to somehow put into one statement if possible. Both statements are for one table.

Statement 1:

SELECT AdminContact, count(*) as Total, Loc
FROM [Form].[dbo].[eForm]
WHERE [AdminContact] IS NOT NULL AND ID = 'Open'
GROUP BY [AdminContact], ALoc

Statement 2:

SELECT AdminContact, count(*) as Total, Loc
FROM [Form].[dbo].[eForm]
WHERE [AdminContact] IS NOT NULL AND ID = 'Open' AND Day(DateRequest) > Day(GetDate())
GROUP BY [AdminContact], ALoc

Both output what I want, but I would like to have it output into one result set.

Is this possible?

Sorry I am just beginning. Thanks for your help.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-06-07 : 01:24:12

quote:
Originally posted by zai

Both output what I want, but I would like to have it output into one result set.

Use Union operator

SELECT AdminContact, count(*) as Total, Loc
FROM [Form].[dbo].[eForm]
WHERE [AdminContact] IS NOT NULL AND ID = 'Open'
GROUP BY [AdminContact], ALoc
Union all
SELECT AdminContact, count(*) as Total, Loc
FROM [Form].[dbo].[eForm]
WHERE [AdminContact] IS NOT NULL AND ID = 'Open' AND Day(DateRequest) > Day(GetDate())
GROUP BY [AdminContact], ALoc

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

naveengopinathasari
Yak Posting Veteran

60 Posts

Posted - 2010-06-07 : 03:44:02

As I understand you want to know the count for
AdminContact and LOC
For the Following 2 Condition i.e,
1. [AdminContact] IS NOT NULL AND ID = 'Open'
2. Day(DateRequest) > Day(GetDate())

Note:* Whats the ALoc and Loc something is wrong in the query.

This is Possible in query if you CASE statement for the second Result

SELECT AdminContact, count(*) as Total, Loc
CASE WHEN Day(DateRequest) > Day(GetDate()) THEN
1
ELSE
0
END AS CountOfAdmIfRequestDateisGreater

FROM [Form].[dbo].[eForm]
WHERE [AdminContact] IS NOT NULL AND ID = 'Open'
GROUP BY [AdminContact], ALoc

I have not tested the Query which i have stated above.
I hope you need to use a SubQuery if you are not able to get the count of the query.
Give the data and your actual query. i can give a solution if you are not able to get one.

Lets unLearn

zai
Starting Member

9 Posts

Posted - 2010-06-07 : 09:27:26

pk_bohra,

The union works but I need the totals from both statements as separate columns in the output. The both statements output 3 columns AdminContact, Total, and Loc. What I am looking for are 4 columns in the output. The column 'total' can be called something different in the final output.

naveengopinathasari,

Yes, you are correct.

Note:* Whats the ALoc and Loc something is wrong in the query.

They both should be Loc.

I think that this may work but I tried it and I'm getting an error regarding incorrect syntex near the keyword 'CASE'.

Here are the two statements corrected.

Statement 1: This outputs total open tickets per admin.

SELECT AdminContact, count(*) as TotalOpen, Loc
FROM [Form].[dbo].[eForm]
WHERE [AdminContact] IS NOT NULL AND ID = 'Open'
GROUP BY [AdminContact], Loc

Statement 2: This outputs total pastdue tickets per admin.

SELECT AdminContact, count(*) as TotalPastDue, Loc
FROM [Form].[dbo].[eForm]
WHERE [AdminContact] IS NOT NULL AND ID = 'Open' AND Day(DateRequest) > Day(GetDate())
GROUP BY [AdminContact], Loc

Does this make sense?

Thank you for both of your times on this topic.

cassell
Starting Member

1 Post

Posted - 2010-06-07 : 09:43:04

SELECT Count (Genre) from game.genre ||''|| game.title as Games_Total
from game
Group By game.genre
;

please tell me what im doing wrong!!!!!!!

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-06-07 : 09:43:39

SELECT AdminContact, count(*) as TotalOpen, Sum(CASE WHEN Day(DateRequest) > Day(GetDate()) THEN 1 END) TotalPastDue, Loc
FROM [Form].[dbo].[eForm]
WHERE [AdminContact] IS NOT NULL AND ID = 'Open'
GROUP BY [AdminContact], Loc

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-06-07 : 09:45:02

quote:
please tell me what im doing wrong!!!!!!!

Well, for starters you're hijacking a thread with a completely different problem. Post your question in its own thread.

You also appear to be posting an Oracle question on a SQL Server forum. You're better off trying dbForums for Oracle questions.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-07 : 10:03:08

quote:
Originally posted by cassell

SELECT Count (Genre) from game.genre ||''|| game.title as Games_Total
from game
Group By game.genre
;

please tell me what im doing wrong!!!!!!!

Have you tried this?

SELECT Count (Genre) as Genre, game.genre ||''|| game.title as Games_Total
from game
Group By game.genre ||''|| game.title

Madhivanan

Failing to plan is Planning to fail

zai
Starting Member

9 Posts

Posted - 2010-06-07 : 10:54:53

robvolk,

This is what I was looking for. Greatly appreciate your response!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources