Help query Count

[EyEciTy]

Hello all,
I swear i have spent hours searching and can't find my answer so I need for the first time a help for this.

I have a table
ID. IDTicket. Status
1. 1233. 0
2. 1233. 0
3. 1234. 1
4. 1234. 1

I want to do a count based on ticket so I have done a simple query
Count IDTicket groupby
Where Status 1

The problem is I get 2 records but I want only 1 since I want to merge IDTicket in one
If I do a query with the groupby first and the a second query with count on first query it works flawless, but I want to do this on just one query, it's possible?

 

[June7]

Do you want to count how many distinct tickets or how many records for each ticket?

For the first, consider a nested query like:
SELECT Count(*) AS CntTick FROM (SELECT DISTINCT IDTicket FROM tablename WHERE Status = 1) AS Q1;

For the latter, don't include ID. Post your actual SQL statement.

 

[plog]

My guess is you put every field in the GROUP BY. You only need to put things in the GROUP BY that you want to....group by:

SELECT IDTicket, COUNT(IDTicket) AS Total
FROM YourTableNameHere
WHERE Status=1
GROUP BY IDTicket

If neither June nor I are correct, you need to post what data you expect your query to return based on the sample data you posted initially. Not words or an explanation, show us what data you expect from the initial data you posted.

 

[theDBguy]

but I want to do this on just one query, it's possible?

It's possible to use a single process if you were using SQL Server but not with Access, I'm afraid.

 

[DHookom]

Hello all,
I swear i have spent hours searching and can't find my answer so I need for the first time a help for this.

I have a table
ID. IDTicket. Status
1. 1233. 0
2. 1233. 0
3. 1234. 1
4. 1234. 1

I want to do a count based on ticket so I have done a simple query
Count IDTicket groupby
Where Status 1

The problem is I get 2 records but I want only 1 since I want to merge IDTicket in one
If I do a query with the groupby first and the a second query with count on first query it works flawless, but I want to do this on just one query, it's possible?

try:

SELECT tblTickets.IDTicket, Count(tblTickets.ID) AS CountOfID
FROM tblTickets
GROUP BY tblTickets.IDTicket, tblTickets.Status
HAVING (((tblTickets.Status)=1));

 

[cheekybuddha]

try:

SELECT tblTickets.IDTicket, Count(tblTickets.ID) AS CountOfID
FROM tblTickets
GROUP BY tblTickets.IDTicket, tblTickets.Status
HAVING (((tblTickets.Status)=1));

Don't use HAVING if the criteria isn't dependent on an aggregation. Use WHERE to filter before the aggregation (COUNT) is applied.

 

[Pat Hartman]

WHERE is applied BEFORE aggregation and can use indexes and joins if available to reduce the initial recordset.
HAVING is applied AFTER aggregation and so the only fields you would ever use in this clause are fields that were aggregated by sum, avg, max, first, group by etc. and those would NEVER use an index plus the aggregated recordset in memory is not indexed.

So, the number of rows you can get rid of with your WHERE will affect the total resources needed by the query.