average age of cliens buying products

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 2000 Forums

SQL Server Development (2000)

average age of cliens buying products

Author

Topic

RoniR
Starting Member

16 Posts

Posted - 2008-12-03 : 13:35:28

hi guys need help with something
i have a table
cli_cde
y_cde n_cde pharamycid CLIENTID date_Cde (...)
and another table for
clients
clientID DateOfBirth (...)

in my cli_cde table i save an order for a client.

I would need some help in quering the tables in order to get a result like this one,lets say

19% of the Clients as 30 years old
1% if the clients are 45 years old and so forth

Can we do this?
thanks for any help
let me know if anything is not clear

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-03 : 13:51:35

something like


SELECT Age,
COUNT(ClientID)*100.0/
(SELECT COUNT(ClientID) FROM clients)
FROM
(
SELECT ClientID,
CASE WHEN ((MONTH(DateOfBirth)<MONTH(GETDATE())) OR 
((MONTH(DateOfBirth)=MONTH(GETDATE())) AND DAY(DateOfBirth)<DAY(GETDATE()))) THEN DATEDIFF(yy,DateOfBirth,GETDATE())-1
ELSE DATEDIFF(yy,DateOfBirth,GETDATE())
END AS Age
FROM clients 
)t
GROUP BY Age

RoniR
Starting Member

16 Posts

Posted - 2008-12-03 : 16:03:39

this is exactly what i need..one thing
lets say my orders tables has 12 order for client 1,9 orders for client 2 ,10 orders for client 3 and 5 orders for client 4

the thing is that i dont want to know the average age of clients from the clients table..i want to to know that from my orders table
25% of the pople are 24 years old

i tried adding that

SELECT Age,
COUNT(ClientID)*100.0/tt.ClientOrders as Average
FROM
(
SELECT ClientID,
CASE WHEN ((MONTH(DateOfBirth)<MONTH(GETDATE())) OR
((MONTH(DateOfBirth)=MONTH(GETDATE())) AND DAY(DateOfBirth)<DAY(GETDATE()))) THEN DATEDIFF(yy,DateOfBirth,GETDATE())-1
ELSE DATEDIFF(yy,DateOfBirth,GETDATE())
END AS Age
FROM clients
)t,(SELECT COUNT(ClientID) as ClientOrders FROM sfc_Cde
group by clientid
) as tt
GROUP BY Age,tt.ClientOrders

and so i got
Age average
----------- ---------------------------------------
22 33.333333333333
23 33.333333333333
24 33.333333333333
25 33.333333333333
22 20.000000000000
23 20.000000000000
24 20.000000000000
25 20.000000000000
22 11.111111111111
23 11.111111111111
24 11.111111111111
25 11.111111111111
22 8.333333333333
23 8.333333333333
24 8.333333333333
25 8.333333333333

instead
SELECT COUNT(ClientID) as ClientOrders FROM sfc_Cde
group by clientid returns

ClientOrders
------------
9
3
5
12
which are the number of orders of each client...
so i wanted to get

100/9 =11.11%
100/3 =33.33%
100/5 =20%
100/12=8.33%

what did i miss and can u please explain to me

thanks a whole lot

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-03 : 23:00:54

may be this

SELECT Age,
COUNT(DISTINCT ClientID)*100.0/
(SELECT COUNT(DISTINCT ClientID) FROM clients c1
INNER JOIN  cli_cde cc1
ON cc1.CLIENT_ID=c1.ClientID)
FROM
(
SELECT ClientID,
CASE WHEN ((MONTH(DateOfBirth)<MONTH(GETDATE())) OR 
((MONTH(DateOfBirth)=MONTH(GETDATE())) AND DAY(DateOfBirth)<DAY(GETDATE()))) THEN DATEDIFF(yy,DateOfBirth,GETDATE())-1
ELSE DATEDIFF(yy,DateOfBirth,GETDATE())
END AS Age
FROM clients c
INNER JOIN  cli_cde cc
ON cc.CLIENT_ID=c.ClientID
)t
GROUP BY Age

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources