Group by and count(*)

[raj]

I have two tables-
vendortable -- Vendor_id and vendor_name ( it has 10 vendor)
Accounttable -- account_id, created_date, vendorid ( it contain user
registartion entry )

I need to find How many user registered between two date for all the
vendorid in vendortable. When I do a Count(*) and group by on the vendorid it
gived me only those vendor which has entry in accounttable.
I tried the right join but same is the case.

If no user is registered for a vendor then I want 0 in the output but it
must show up in result

my query is like this--


SELECT vendortable.Vendor_id, Count(Nz([account_id])) AS Expr1
FROM vendortable INNER JOIN dbo_accounts ON vendortable.Vendor_id =
dbo_accounttable.vendor_id
WHERE (((dbo_accounttable.created_date)>=[Start Date] And
(dbo_accounttable.created_date)<[End Date]))
GROUP BY PrivateLabelReportVendor.Vendor_id;


Ant help greatly appriciated.

rajesh

 

[Marshall Barton]

I have two tables-
vendortable -- Vendor_id and vendor_name ( it has 10 vendor)
Accounttable -- account_id, created_date, vendorid ( it contain user
registartion entry )

I need to find How many user registered between two date for all the
vendorid in vendortable. When I do a Count(*) and group by on the vendorid it
gived me only those vendor which has entry in accounttable.
I tried the right join but same is the case.

If no user is registered for a vendor then I want 0 in the output but it
must show up in result

my query is like this--


SELECT vendortable.Vendor_id, Count(Nz([account_id])) AS Expr1
FROM vendortable INNER JOIN dbo_accounts ON vendortable.Vendor_id =
dbo_accounttable.vendor_id
WHERE (((dbo_accounttable.created_date)>=[Start Date] And
(dbo_accounttable.created_date)<[End Date]))
GROUP BY PrivateLabelReportVendor.Vendor_id;

You need to use a LEFT JOIN and get rid of the NZ function:

SELECT vendortable.Vendor_id, Count([account_id]) AS Expr1
FROM vendortable LEFT JOIN dbo_accounts
ON vendortable.Vendor_id = dbo_accounttable.vendor_id
WHERE (((dbo_accounttable.created_date)>=[Start Date]
And (dbo_accounttable.created_date)<[End Date]))
GROUP BY PrivateLabelReportVendor.Vendor_id

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Use LEFT JOIN:

SELECT V.Vendor_id, Count(A.account_id) AS VendorCount
FROM vendortable V LEFT JOIN dbo_accounts A
ON V.Vendor_id = A.vendor_id
WHERE A.created_date >= [Start Date] And A.created_date < [End Date]
GROUP BY V.Vendor_id;

You don't need Nz(account_id), 'cuz Count() evaluates NULLs as zero.
Also, by setting the account_id to zero, which is what Nz(account_id)
would do, you are actually counting NULL account_ids. E.g.:

Count(NULL) = 0
Count(Nz(Null)) = 1

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQf/wx4echKqOuFEgEQLrWgCfcmSPubxXLYaJOoyk2BOxC47TBu0An3G2
KkGd2YESDd+xgYf07LXjH//W
=GAiu
-----END PGP SIGNATURE-----