Two SELECT...GROUP BY queries into one query
-
Hi,
I am working on this query to generate a report on number of users who registered with us and number of members who cancelled. Users table has two fields, registerdate and canceldate, thats relevant for this query.
I am able to do this in two seperate queries, one for registrations and one for cancellations but I am not able to figure out how to combine the two into one query.
This is what I have.
SELECT COUNT(*) AS Registrations, CONVERT(VARCHAR, registerdate, 101)
FROM users WHERE registerdate > @startdate AND registerdate < @enddate
GROUP BY CONVERT(VARCHAR, registerdate, 101)SELECT COUNT(*) AS Cancellations, CONVERT(VARCHAR, canceldate, 101)
FROM users WHERE canceldate > @startdate AND canceldate < @enddate
GROUP BY CONVERT(VARCHAR, canceldate, 101)Can any one tell me how to do this in a single query. I want the resultset to have three columns - Registrations, Cancellations, Date.
Thanks.
-
Hi,
Use the folowing Query to solve your problem : it is slightly tricky::
SELECT 'R' Type ,COUNT(*) AS Records, CONVERT(VARCHAR, registerdate, 101)
FROM users WHERE registerdate > @startdate AND registerdate < @enddate
GROUP BY CONVERT(VARCHAR, registerdate, 101)
Union all
SELECT 'C' Type , COUNT(*) AS Records, CONVERT(VARCHAR, canceldate, 101)
FROM users WHERE canceldate > @startdate AND canceldate < @enddate
GROUP BY CONVERT(VARCHAR, canceldate, 101)
Enjoy:::::
Regards,
Amit Gupta
-
Hi,
What you suggested, it will have all the registrations followed by all the cancellations. I would like to have the registrations and cancellations number side by side. It will be easy to compare.
Thanks.
-
SELECT ISNULL(R.RegisterDate,C.CancelDate) AS TranDate, R.Registrations, C.Cancellations
FROM
(SELECT COUNT(*) AS Registrations, CONVERT(VARCHAR, registerdate, 101) AS RegisterDate
FROM users WHERE registerdate > @startdate AND registerdate @startdate AND canceldate < @enddate
GROUP BY CONVERT(VARCHAR, canceldate, 101) ) C
ON R.RegisterDate = C.CancelDate
-
Senthil,
May not be the most efficient but should work for you. It will report each date in the range whether there were registrations / cancellations or not.
Mike
DECLARE
@startdate DATETIME,
@enddate DATETIME,
@effectivedate DATETIME
DECLARE
@Users TABLE(
registerdate DATETIME,
canceldate DATETIME)
--
INSERT @Users SELECT '5/1/06', NULL
INSERT @Users SELECT NULL, '5/1/06'
INSERT @Users SELECT '5/2/06','5/5/06'
--
SELECT
@startdate = '5/1/06',
@enddate= '5/5/06'
-----------------------------------------------------------------
DECLARE
@Report TABLE(
EffectiveDate DATETIME,
Registrations INT,
Cancellations INT)
--
SELECT
@EffectiveDate = @startdate
--
WHILE
@EffectiveDate <= @enddate
BEGIN
INSERT @Report
SELECT
@EffectiveDate,
(SELECT COUNT(*) FROM @Users WHERE registerdate = @EffectiveDate),
(SELECT COUNT(*) FROM @Users WHERE canceldate = @EffectiveDate )
SELECT
@EffectiveDate = DATEADD(DAY,1,@EffectiveDate)
END
-- Output
SELECT
CONVERT(VARCHAR,EffectiveDate,101) AS 'Date',
Registrations,
Cancellations
FROM
@Report
ORDER BY
EffectiveDate
-
Thanks very much.
-
Here is the real query with some optimization techniques...
SELECT SUM(CASE WHEN DT.type = 'r' THEN DT.Items ELSE 0 END) Registrations,
SUM(CASE WHEN DT.type = 'c' THEN DT.Items ELSE 0 END) Cancellations,
CONVERT(VARCHAR, DT.theDate, 101) theDate
FROM (
SELECT canceldate theDate,
'c' type,
COUNT(*) Items
FROM users
WHERE canceldate > @startdate
AND canceldate < @enddate
GROUP BY canceldate
UNION ALL
SELECT registerdate,
'r',
COUNT(*)
FROM users
WHERE registerdate > @startdate
AND registerdate < @enddate
GROUP BY registerdate
) DT
GROUP BY DT.theDate
ORDER BY DT.theDate
N 56°04'39.16"
E 12°55'05.25" -
Hi Joe!
I have read your books on SQL for smarties over and over again! Never thought I met you online.
Sorry to say there is a glitch in your query.
Suppose @start_date is 6/1/2005 and @end_date is 6/30/2005
and a user has a record with registerdate 6/2/2005 and a canceldate 7/15/2005,
then your query reports both registration and cancellation of this customer, even if canceldate is out of range but still not NULL.
Compare with the original posting.
Also, we don't know the purpose of the reason of having the date converted as 101. Maybe he want to output the result into a file for further processing in some other system?
N 56°04'39.16"
E 12°55'05.25" -
Erm... sorry but the user is asking for registrations and cancellations PER DAY.
Joe, Your query is NOT doing that
* Noel -
For that query, we will need a calendar table with a search condition like... Senthil,
Joe is absolutely correct about needing a "Calendar" table. To clarify a bit, most of the queries offered to solve your problem do not take into account that there may be days in your range of dates that have no rows (tuples, etc, whatever) . Because of that, those days will simply not show up. Joe is suggesting the use of a "Calendar Table" to solve the problem of displaying days with no rows found.
Just in case you don't know, a "Calendar Table" can come in many forms from very simple to somewhat complex. The most simple Calendar Table contains nothing but a single column (attribute, etc, whatever) of sequential dates. Complex Calendar tables contain that and other columns such as the spelled out day of the week, holidays, and much, much more.
There's a similar class of tables called "Number" or "Tally" tables and they consist of a single column of sequential integers that usually start at 1 and go up to some number (I use 100,000 because of other requirements... most make them up to 8000 or 9999). Number or Tally tables have a huge number of uses, as well, and you can find a large number of those uses by doing a search on this forum.
I use a table that is both a Tally table and a Calendar table... I still call it a "Tally" table even though it also has calendar information in it. Here's an easy way to make one... and, yes, you should convince your DBA that it should be a permanent public table...
--===== Set the first day of the week to Monday
SET DATEFIRST 1
--===== Create and populate the Tally table with numbers on the fly
SELECT TOP 100000
IDENTITY(INT,1,1) AS N, --Sequential Number
CAST(NULL AS DATETIME) AS Dt, --Sequential Date
CAST(NULL AS VARCHAR(9)) AS Dow, --Spelled out Day-of-Week
CAST(NULL AS INT) AS Dw, --Numeric day of week (Mon = 1)
CAST(NULL AS INT) AS Wk --Sequential Numeric Week
INTO dbo.Tally
FROM Master.dbo.SysComments sc1,
Master.dbo.SysComments sc2
--===== Add the date to the table
UPDATE dbo.Tally
SET Dt = CAST(N AS DATETIME)
--===== Add the rest of the date info
UPDATE dbo.Tally
SET Dow = DATENAME(dw,Dt),
Dw = DATEPART(dw,Dt),
Wk = DATEDIFF(wk,0,Dt-1) --LOOK!!! Change this if you change DateFirst!
--===== Add a Primary Key to maximize performance for numeric joins
ALTER TABLE dbo.Tally
ADD CONSTRAINT PK_Tally_N PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100
--===== Add an index to maximize performance for date joins
CREATE INDEX IX_Tally_Dt
ON dbo.Tally(Dt) WITH FILLFACTOR = 100
--===== Allow the general public to use it
GRANT SELECT ON dbo.Tally TO PUBLIC
--===== Display the space used by the Tally table
EXEC dbo.sp_SpaceUsed 'Tally'
A couple of things to keep in mind because I made this table especially for the types of things I have to do...
- You can decide what columns to create or not. I didn't need holidays or ISO weeks, etc.
- Because of my situation, my weeks start on Monday, not the default of Sunday. Use the DATEFIRST setting at the beginning of the code to change that.
- You may need other indexes. You should make sure they have a FILLFACTOR of 100 because this is a STATIC table that won't change. The FILLFACTOR of 100 saves storage space and makes access to the table even faster because it doesn't have to consider blank space caused by lesser fill factors.
- You can call your columns and the table whatever you want. I'm just showing you what I need to use as an example. For example, Joe called his table "Calendar" and his date column "cal_date".
- My table goes from 1 to 100,000 and the dates go from 19000102 to 21731016 (yyyymmdd). Why so big? I do a lot of things with dates up to about 100 years old and 100 years into the future. 100,000 days covered that and was a nice round number so that's what I used. You can certainly change that but watch the date creation formula. You'll probably need to use something like DT=CAST(startdatestring AS DATETIME) -N-1.
- I used INT as the data-type in the table in a couple of places where you might want to use SMALLINT or TINYINT. The reason I used INT instead was to keep others from writing performance challenged code due to implicit data conversions. Most of them do everything with VARCHAR and INT... I know... it's dumb but I'm not the DBA for the group of people I built the table for and I'm still trying to show them the error of their ways... you know how folks can be sometimes
Now that you understand what a calendar table looks like, understand that if you join it by date to another table by date, you can detect and account for any dates that may be missing and give them a zero kinda like Joe did.
Write back if you have any questions.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply