Grouping Problem - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Grouping Problem

Author

Topic

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2009-01-15 : 04:18:02

Hi Guys

I am using the following query:

SELECT
c.Gender

,(SELECT COUNT(cus.Customerid)
FROM
(
SELECT c2.CustomerID, c2.Gender
FROM dbo.Customer AS c2 WITH (NOLOCK)
LEFT JOIN dbo.Receipt AS r WITH (NOLOCK)
ON c2.CustomerID = r.CustomerID
WHERE r.DateEntered BETWEEN @StartDate AND DATEADD(s,-1,DATEADD(d,+1,@EndDate))
AND r.StatusId = 90000
GROUP BY c2.CustomerID, c2.Gender
)cus
WHERE c.Gender = Cus.Gender) AS 'No. of Customers'

,(SELECT COUNT(op.ReceiptID)
FROM
(
SELECT r.ReceiptID, r.CustomerID, c.Gender
FROM dbo.Receipt AS r WITH (NOLOCK)
LEFT JOIN dbo.Customer AS c WITH (NOLOCK)
ON r.CustomerID = c.CustomerID
WHERE r.DateEntered BETWEEN @StartDate AND DATEADD(s,-1,DATEADD(d,+1,@EndDate))
AND r.StatusId = 90000
)op
WHERE c.Gender = op.Gender) AS 'No. of Orders Placed'

,(SELECT SUM(ov.PriceIncTax)
FROM
(
SELECT ri.PriceIncTax, c.Gender
FROM dbo.Receipt AS r WITH (NOLOCK)
LEFT JOIN dbo.ReceiptItem AS ri WITH (NOLOCK)
ON r.ReceiptID = ri.ReceiptID
LEFT JOIN dbo.Customer AS c WITH (NOLOCK)
ON r.CustomerID = c.CustomerID
WHERE r.DateEntered BETWEEN @StartDate AND DATEADD(s,-1,DATEADD(d,+1,@EndDate))
AND r.StatusId = 90000
)ov
WHERE c.Gender = ov.Gender) AS 'Total Order Value'

FROM dbo.Customer AS c

GROUP BY c.Gender

Which gives me the following results:
Gender, No. of Customers, No. of Orders PLaced, Total Order Value
F, 148468, 171545, 8768084.57
M, 30791, 34676, 1946949.10

The thing I am trying to do is split the Gender furthur as 'New Customer' and 'Existing Customer'

So I want it to look like the following
(sample data)
Gender, Customer Type, No. of Customers, No. of Orders PLaced, Total Order Value
F,New Customer, 700, 500 10000.00
F,Existing Customer , 700, 400, 9000.00
M,New Customer, 300, 250, 300, 7000.00
M, Existing Customer, 175, 150, 100, 5000.00

I added the following CASE statement to the 'SELECT' section:
,CASE
WHEN c.DateEntered <= DATEADD(m,-1,GETDATE()) THEN 'New Customers'
WHEN c.DateEntered > DATEADD(m,-1,GETDATE()) THEN 'Existing Customer'
END AS 'Customer Type'

Also I had to enter c.DateEntered to the 'Group By', but when I run the query I get the incorrect layout, for example:
M New Customers 30791 34676 1946949.10
M New Customers 30791 34676 1946949.10
M New Customers 30791 34676 1946949.10
F New Customers 148468 171545 8768084.57
F New Customers 148468 171545 8768084.57
F Existing Customer 148468 171545 8768084.57
F Existing Customer 148468 171545 8768084.57
F New Customers 148468 171545 8768084.57
F New Customers 148468 171545 8768084.57
F New Customers 148468 171545 8768084.57

Does anyone have any idea how I could get it to display to the way I originally wanted it to look?

Thanking you in advance!

thiyagu_rind
Starting Member

46 Posts

Posted - 2009-01-15 : 05:26:21

Hi Dear,

Please give a clear picture about your problem. I hope no one understands your problem.

Regards
Thiyagarajan

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2009-01-15 : 05:32:16

Hi

I want my results to come out in the following format:

Gender, Customer Type, No. of Customers, No. of Orders PLaced, Total Order Value
F,New Customer, 700, 500 10000.00
F,Existing Customer , 700, 400, 9000.00
M,New Customer, 300, 250, 300, 7000.00
M, Existing Customer, 175, 150, 100, 5000.00

As opposed to the way it is currently coming out:

Gender, Customer Type, No. of Customers, No. of Orders PLaced, Total Order Value
M New Customers 30791 34676 1946949.10
M New Customers 30791 34676 1946949.10
M New Customers 30791 34676 1946949.10
F New Customers 148468 171545 8768084.57
F New Customers 148468 171545 8768084.57
F Existing Customer 148468 171545 8768084.57
F Existing Customer 148468 171545 8768084.57
F New Customers 148468 171545 8768084.57
F New Customers 148468 171545 8768084.57
F New Customers 148468 171545 8768084.57

I hope this makes it clearer!

Thanks

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-01-15 : 05:49:18

Instead of putting c.DateEntered to your group by, Put this :

CASE
WHEN c.DateEntered <= DATEADD(m,-1,GETDATE()) THEN 'New Customers'
WHEN c.DateEntered > DATEADD(m,-1,GETDATE()) THEN 'Existing Customer'
END

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2009-01-15 : 05:55:18

Hi

Thanks for that!

I put this in the 'Group By' but the figures are just duplicating now:

F, New Customers, 148473, 171556, 8768120.57, 30896.45, 832
F, Existing Customer, 148473, 171556, 8768120.57, 30896.45, 832
M, Existing Customer, 30793, 34682, 1947178.26, 11389.40, 175
M, New Customers, 30793, 34682, 1947178.26, 11389.40, 175

Any ideas?

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-01-15 : 06:23:38

Your data must be like that. With your earlier result too, the values looked similar.

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2009-01-15 : 06:57:12

Hi

I broke down the wuery for testing and it seemed to come out with the results I wanted:

SELECT
c2.Gender
,CASE
WHEN c2.DateEntered <= DATEADD(m,-1,GETDATE()) THEN 'New Customers'
WHEN c2.DateEntered > DATEADD(m,-1,GETDATE()) THEN 'Existing Customer'
END AS 'Customer Type'
,COUNT(c2.CustomerID)
FROM dbo.Customer AS c2 WITH (NOLOCK)
LEFT JOIN dbo.Receipt AS r WITH (NOLOCK)
ON c2.CustomerID = r.CustomerID
WHERE r.DateEntered BETWEEN @StartDate AND DATEADD(s,-1,DATEADD(d,+1,@EndDate))
AND r.StatusId = 90000
GROUP BY
c2.Gender
,CASE
WHEN c2.DateEntered <= DATEADD(m,-1,GETDATE()) THEN 'New Customers'
WHEN c2.DateEntered > DATEADD(m,-1,GETDATE()) THEN 'Existing Customer'
END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-15 : 07:13:35

quote:
Originally posted by rcr69er

Hi

I broke down the wuery for testing and it seemed to come out with the results I wanted:

SELECT
c2.Gender
,CASE
WHEN c2.DateEntered <= DATEADD(m,-1,GETDATE()) THEN 'New Customers'
WHEN c2.DateEntered > DATEADD(m,-1,GETDATE()) THEN 'Existing Customer'
END AS 'Customer Type'
,COUNT(c2.CustomerID)
FROM dbo.Customer AS c2 WITH (NOLOCK)
LEFT JOIN dbo.Receipt AS r WITH (NOLOCK)
ON c2.CustomerID = r.CustomerID
WHERE r.DateEntered BETWEEN @StartDate AND DATEADD(s,-1,DATEADD(d,+1,@EndDate))
AND r.StatusId = 90000
GROUP BY
c2.Gender
,CASE
WHEN c2.DateEntered <= DATEADD(m,-1,GETDATE()) THEN 'New Customers'
WHEN c2.DateEntered > DATEADD(m,-1,GETDATE()) THEN 'Existing Customer'
END

also this which is much neater


SELECT Gender,Customer Type,COUNT(CustomerID) AS CountVal
FROM
(
SELECT 
c2.Gender
,CASE
WHEN c2.DateEntered <= DATEADD(m,-1,GETDATE()) THEN 'New Customers'
WHEN c2.DateEntered > DATEADD(m,-1,GETDATE()) THEN 'Existing Customer'
END AS 'Customer Type',
c2.CustomerID
FROM dbo.Customer AS c2 WITH (NOLOCK)
LEFT JOIN dbo.Receipt AS r WITH (NOLOCK)
ON c2.CustomerID = r.CustomerID
WHERE r.DateEntered BETWEEN @StartDate AND DATEADD(s,-1,DATEADD(d,+1,@EndDate)) 
AND r.StatusId = 90000
)t
GROUP BY 
Gender,Customer Type

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2009-01-15 : 07:31:21

Thanks Visakh

Do you know what I would have to change in my original query at the top to get the desired results?

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources