Distinct Counting 2 level grouping -SQL please!

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Please help!
I am trying to accomplish 2 distinct counts for two fields in 1 query. Is
this possible? I would appreciate the SQL view accomplishing this.

I currently run 2 queries,
1) grouping by Reg & HH then counting Pol & summing Amt then run
2) grouping by Reg, count HH, sum polcount and amt

For example, my data is as follows 4 fields, Reg, HH, Pol, Amt

Reg HH Pol Amt
NE Bill 2 $10
SW Jane 8 $12
NE Bill 7 $5
SC Rob 3 $50
NE Tim 9 $5
SW Jill 1 $5

The end result of my query would count within each region the distinct # of
HH and # of policies and sum up the amounts
Should be as follows

Reg DistinctHH CountofPol SumofAmt
NE 2 3 $20
SW 2 2 $17
SC 1 1 $50
 
jennifer said:
I am trying to accomplish 2 distinct counts for two fields in 1 query. Is
this possible? I would appreciate the SQL view accomplishing this.

I currently run 2 queries,
1) grouping by Reg & HH then counting Pol & summing Amt then run
2) grouping by Reg, count HH, sum polcount and amt

For example, my data is as follows 4 fields, Reg, HH, Pol, Amt

Reg HH Pol Amt
NE Bill 2 $10
SW Jane 8 $12
NE Bill 7 $5
SC Rob 3 $50
NE Tim 9 $5
SW Jill 1 $5

The end result of my query would count within each region the distinct # of
HH and # of policies and sum up the amounts
Should be as follows

Reg DistinctHH CountofPol SumofAmt
NE 2 3 $20
SW 2 2 $17
SC 1 1 $50
Click to expand...


I think you do need two queries to do that.

First create a query to get the distinct HH:
qryDistinctHH:
SELECT DISTINCT Reg, HH
FROM thetable

Then this query will provide the desired results
SELECT T.Reg,
Y.DistinctHH,
Count(*) AS CountOfPol,
Sum(T.Amt) AS SumOfAmt
FROM thetable As T INNER JOIN
[SELECT X.Reg, Count(*) As DistinctPol
FROM qryDistinctHH As X
GROUP BY X.Reg]. Y
ON Y.Reg = T.Reg
GROUP BY T.Reg, Y.DistinctHH

(Note the dot after the square bracket)
 
Marshall -

I'm getting an error: my DISTINCT query is called qry_WorkProjects

qry_TimeReportProjects-------------------------------------------------------------------
SELECT DISTINCT WorkProject, WorkHours, Employee, WorkMonth
FROM tbl_TimeReport INNER JOIN tbl_TimeDetails ON
tbl_TimeReport.TimeReportID = tbl_TimeDetails.TimeReportID
WHERE (((tbl_TimeReport.TimeReportEmployee) Like [Enter employee's full
name]) AND ((tbl_TimeReport.TimeReportMonth) Like [For what month?]))
ORDER BY tbl_TimeDetails.WorkProject;

produces (pretty much):
**NOTE: results are not DISTINCT = problem #1 :(

WorkProject WorkHours
ProjectA 4
ProjectA 5
ProjectA 6
ProjectA 10
ProjectB 4
ProjectB 8

qry_WorkProjectHours---------------------------------------------------------------------
SELECT T.WorkProject, Y.QRY_TimeReportProjects, Sum(T.WorkHours) AS
SumOfWorkHours
FROM tbl_TimeDetails As T INNER JOIN
[SELECT X.WorkProject
FROM QRY_TimeReportProjects As X
GROUP BY X.WorkProject]. Y
ON Y.WorkProject = T.WorkProject
GROUP BY T.WorkProject, Y.QRY_TimeReportProjects

-- so, as expected via qry_WorkProjects, I am asked to enter the employee's
name and the month. however then I am asked to enter a value for
Y.QRY_TimeReportProjects. I throw in "*" to return all and it works, but why
am I getting this? Any ideas?


Thanks for the code here!
/amelia
 
aaearhart said:
I'm getting an error: my DISTINCT query is called qry_WorkProjects

qry_TimeReportProjects-------------------------------------------------------------------
SELECT DISTINCT WorkProject, WorkHours, Employee, WorkMonth
FROM tbl_TimeReport INNER JOIN tbl_TimeDetails ON
tbl_TimeReport.TimeReportID = tbl_TimeDetails.TimeReportID
WHERE (((tbl_TimeReport.TimeReportEmployee) Like [Enter employee's full
name]) AND ((tbl_TimeReport.TimeReportMonth) Like [For what month?]))
ORDER BY tbl_TimeDetails.WorkProject;

produces (pretty much):
**NOTE: results are not DISTINCT = problem #1 :(

WorkProject WorkHours
ProjectA 4
ProjectA 5
ProjectA 6
ProjectA 10
ProjectB 4
ProjectB 8

qry_WorkProjectHours---------------------------------------------------------------------
SELECT T.WorkProject, Y.QRY_TimeReportProjects, Sum(T.WorkHours) AS
SumOfWorkHours
FROM tbl_TimeDetails As T INNER JOIN
[SELECT X.WorkProject
FROM QRY_TimeReportProjects As X
GROUP BY X.WorkProject]. Y
ON Y.WorkProject = T.WorkProject
GROUP BY T.WorkProject, Y.QRY_TimeReportProjects

-- so, as expected via qry_WorkProjects, I am asked to enter the employee's
name and the month. however then I am asked to enter a value for
Y.QRY_TimeReportProjects. I throw in "*" to return all and it works, but why
am I getting this? Any ideas?

I think you do need two queries to do that.

First create a query to get the distinct HH:
qryDistinctHH:
SELECT DISTINCT Reg, HH
FROM thetable

Then this query will provide the desired results
SELECT T.Reg,
Y.DistinctHH,
Count(*) AS CountOfPol,
Sum(T.Amt) AS SumOfAmt
FROM thetable As T INNER JOIN
[SELECT X.Reg, Count(*) As DistinctPol
FROM qryDistinctHH As X
GROUP BY X.Reg]. Y
ON Y.Reg = T.Reg
GROUP BY T.Reg, Y.DistinctHH

(Note the dot after the square bracket)
Click to expand...
Click to expand...


Something got mangled in the translation from your original
post to my reply and on into your last attempt ;-)

I think problem #1 is because you included WorkMonth in the
field list (with its Show box checked). I can't tell for
sure because you didn't paste all the results, but that's
what it looks like from here. It's unclear why you are
using the Like operator (instead of =) on the WorkMonth
criteria. Also note that an Order By clause not only won't
serve any useful purpose in theis query, but it will also
make the query run slower.

qry_TimeReportProjects-------------------------------------------------------------------
SELECT DISTINCT WorkProject, WorkHours
FROM tbl_TimeReport INNER JOIN tbl_TimeDetails
ON tbl_TimeReport.TimeReportID =
tbl_TimeDetails.TimeReportID
WHERE (tbl_TimeReport.TimeReportEmployee =
[Enter employee's full name])
AND (tbl_TimeReport.TimeReportMonth =
[For what month?])

qry_WorkProjectHours---------------------------------------------------------------------
SELECT T.WorkProject,
Y.DistinctPol,
Sum(T.WorkHours) AS SumOfWorkHours
FROM tbl_TimeDetails As T INNER JOIN
[SELECT X.WorkProject,
Count(*) As DistinctPol
FROM QRY_TimeReportProjects As X
GROUP BY X.WorkProject]. Y
ON Y.WorkProject = T.WorkProject
GROUP BY T.WorkProject, Y.DistinctPol
 
Back
Top