| Author |
 Topic  |
|
CarKnee
Junior Member
USA
297 Posts |
 Posted - 23 October 2003 : 11:49:47
|
I have the following tables:
downloads
-----
D_ID (autonumber)
U_ID
P_ID
F_ID
users
-----
U_ID (auto)
U_region
...
products
----
P_ID (auto)
...
I need to do the following:
select distinct U_ID, P_ID, F_ID from downloads and do a COUNT of D_ID GROUPing BY U_region
I am going craxy trying to get distinct and count to work at the same time.
Can anyone help?
Thanks,
CarKnee
|
 |
|
|
davemaxwell
Access 2000 Support Moderator
USA
3020 Posts |
Posted - 23 October 2003 : 14:34:04
|
| If you're trying to do both at the same time for different criteria, you're out of luck because DISTINCT and GROUP BY do the same thing. The only difference is GROUP BY can return a count, where DISTINCT does not. |
Dave Maxwell
Barbershop Harmony Freak |
 |
|
|
CarKnee
Junior Member
USA
297 Posts |
Posted - 24 October 2003 : 11:21:32
|
Here is what I have:
--
SELECT DISTINCT
USERS_LEADSYS_DOMESTIC_ZIPS.Z_CountryCode AS Region,
USERS_DOWNLOADS.U_ID, USERS_DOWNLOADS.F_ID,
USERS_DOWNLOADS.P_ID
FROM
USERS_USERS U INNER JOIN
USERS_LEADSYS_DOMESTIC_ZIPS ON
CONVERT(int, U.U_Zip) BETWEEN
USERS_LEADSYS_DOMESTIC_ZIPS.Z_Start AND
USERS_LEADSYS_DOMESTIC_ZIPS.Z_End INNER JOIN
USERS_DOWNLOADS ON
U.U_ID = USERS_DOWNLOADS.U_ID INNER JOIN
PRODUCTS_PRODUCTS ON
USERS_DOWNLOADS.P_ID = PRODUCTS_PRODUCTS.P_ID
It returns this:
-----
Region U_ID F_ID P_ID
------------------------------------
mid-west 4234 224 62
ne-canada 8695 226 59
mid-west 12259 227 64
mid-west 13957 111 63
ne-canada 15585 112 66
What I need is the region name, and the count of the regions
Region Count
------------------------------------
mid-west 3
ne-canada 2
Any ideas?
Thanks,
CarKnee |
|
Edited by - CarKnee on 24 October 2003 11:22:48 |
|
|
|
D3mon
Senior Member
United Kingdom
1685 Posts |
|
|
CarKnee
Junior Member
USA
297 Posts |
Posted - 24 October 2003 : 11:45:07
|
When I do a COUNT and Group by on that query it wont work because I need to include the other fields in the group by, hence giving me not what I need.
|
|
|
|
|
D3mon
Senior Member
United Kingdom
1685 Posts |
|
|
D3mon
Senior Member
United Kingdom
1685 Posts |
|
|
CarKnee
Junior Member
USA
297 Posts |
Posted - 24 October 2003 : 12:18:18
|
You cant do a GROUP BY on just Region and Count without including the U_ID, F_ID & P_ID fields in it as well.
I am thinking of doing a stored procedure to insert into a temp table or something... but it is Friday. Why do something Friday when you can put it off until Monday? Especially when it is your birthday. 
Thanks,
CarKnee |
|
|
|
|
D3mon
Senior Member
United Kingdom
1685 Posts |
|
|
CarKnee
Junior Member
USA
297 Posts |
Posted - 24 October 2003 : 12:48:08
|
Thanks.
Yes, but not for much longer! |
|
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 24 October 2003 : 13:49:16
|
Try this:
SELECT USERS_LEADSYS_DOMESTIC_ZIPS.Z_CountryCode As Region, COUNT (*) FROM
USERS_USERS U INNER JOIN
USERS_LEADSYS_DOMESTIC_ZIPS ON
CONVERT(int, U.U_Zip) BETWEEN
USERS_LEADSYS_DOMESTIC_ZIPS.Z_Start AND
USERS_LEADSYS_DOMESTIC_ZIPS.Z_End INNER JOIN
USERS_DOWNLOADS ON
U.U_ID = USERS_DOWNLOADS.U_ID INNER JOIN
PRODUCTS_PRODUCTS ON
USERS_DOWNLOADS.P_ID = PRODUCTS_PRODUCTS.P_ID
Group By USERS_LEADSYS_DOMESTIC_ZIPS.Z_CountryCode
I think this should do what you want.
Happy birthday. |
Snitz 3.4 Readme | Like the support? Support Snitz too |
Edited by - ruirib on 24 October 2003 13:52:16 |
|
|
|
CarKnee
Junior Member
USA
297 Posts |
Posted - 24 October 2003 : 14:31:19
|
Hey ruirib,
Thanks... However, that seems to be the same as
USERS_LEADSYS_DOMESTIC_ZIPS.Z_CountryCode As Region, COUNT (Z_COUNTRYCODE)
isnt it?
It isnt eliminating duplicate U_ID, P_ID and F_ID
tHANKS!
CarKnee |
|
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 24 October 2003 : 15:05:56
|
| I don't think it eliminates duplicates, no. It just counts the records, grouped by Region (Z_CountryCode), isn't that what you want? And yes, I think it is similar to the other query. |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
CarKnee
Junior Member
USA
297 Posts |
Posted - 24 October 2003 : 15:09:31
|
I was able to get the counts of all records, but I really needed to eliminate the duplicate U_ID, F_ID & P_ID records.
Thanks.
|
|
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 24 October 2003 : 16:54:30
|
| What database are you using for that? I'll get you a subquery to achieve what you want, but I need to know if your DB supports it. |
Snitz 3.4 Readme | Like the support? Support Snitz too |
Edited by - ruirib on 24 October 2003 16:54:51 |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 24 October 2003 : 17:03:03
|
Something like this should do it:
SELECT Z_CountryCode As Region, COUNT (*)
FROM (SELECT DISTINCT
USERS_LEADSYS_DOMESTIC_ZIPS.Z_CountryCode AS Region,
USERS_DOWNLOADS.U_ID, USERS_DOWNLOADS.F_ID,
USERS_DOWNLOADS.P_ID
FROM
USERS_USERS U INNER JOIN
USERS_LEADSYS_DOMESTIC_ZIPS ON
CONVERT(int, U.U_Zip) BETWEEN
USERS_LEADSYS_DOMESTIC_ZIPS.Z_Start AND
USERS_LEADSYS_DOMESTIC_ZIPS.Z_End INNER JOIN
USERS_DOWNLOADS ON
U.U_ID = USERS_DOWNLOADS.U_ID INNER JOIN
PRODUCTS_PRODUCTS ON
USERS_DOWNLOADS.P_ID = PRODUCTS_PRODUCTS.P_ID
)
Group By Z_CountryCode
This should work for Access. For SQL Server it will need a small change. |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
Topic |
|
Topic Locked
