Problem with select with compute

[ldejesuspr]

I have the following statement and in the total column i expect the count of all the rows, instead it counts only itself

Expertise on this will be appreciated

SELECT count(UNIOGEN.numss) as total, uniogen.age, UNIOGEN.fkclasif, UNIOGEN.fkport

FROM dbo.UNIOGEN where UNIOGEN.age between 26 and 35 and
uniogen.numss not in (select depemp.numss from depemp where depemp.parentesco like 'espos')

group by numss, uniogen.age, UNIOGEN.fkclasif, UNIOGEN.fkport

 

[cLFlaVA]

What is "itself"?
This COUNT will only count the number of rows returned based on the WHERE clause in your statement.

*cLFlaVA
----------------------------
A polar bear walks into a bar and says, "Can I have a ... beer?"
The bartender asks, "What's with the big pause?

 

[amorous]

If you want the count of all the rows then you need something like this:

SELECT Count(*) as totalrows FROM dbo.UNIOGEN where UNIOGEN.age between 26 and 35 and
uniogen.numss not in (select depemp.numss from depemp where depemp.parentesco like 'espos')

not clear what you actually want

-VJ

 

[ldejesuspr]

It is suppose to return the count but instead i get 400 rows with the number 1. This is the problem i am having

 

[Pattycake245]

Is the UNIOGEN.numss a unique field? Maybe the fact that you have the numss in your group by is causing the problem.

In your query you are saying you want to group by all four fields. You may be getting 400 results with a count of 1 because each row returned is unique. Amorous's solution will give you a total count, unless as he said you are looking for something else. Maybe post what you want the data to look like, that might help.

Tim

 

[SQLBill]

Why do you have GROUP BY? That is what is causing your problem.

Let's take a look at how SQL Server 'runs' your query....

First SQL Server gathers the all the rows that meet the WHERE clause.

Next it groups all of those rows based on the GROUP BY.

Then it does any aggregate functions (COUNT, SUM, AVG, MAX, etc) from the SELECT.

Lastly it returns the data based upon the SELECT.

-SQLBill

Posting advice: FAQ481-4875

 

[ESquared]

I hope you don't mind me poking my nose in here after other people have been answering...

ldejesuspr,

your query looks fine. Maybe it would help to review how GROUP BY works? Let me give an example. Here's a table called Salaries:

[tt]EmpID Dept Salary
1 1 45000
2 1 90000
3 1 60000
4 2 16000
5 2 24000
6 2 22500
7 3 135000
8 3 127500[/tt]

I know which department I want to work in! Let's say we want to see the minimum, maximum, average, and sum of each department's salaries, with the number of salaries paid out:

SELECT
      Dept,
      Lowest = Min(Salary),
      Highest = Max(Salary),
      Average = Avg(Salary),
      DeptCost = Sum(Salary),
      Headcount = Count(*)
   FROM Salaries
   GROUP BY Dept

---- Result set:
Dept  Lowest  Highest  Average  DeptCost  Headcount
 1     45000   90000    65000   195000     3
 2     16000   24000    20833    62500     3
 3    127500  135000   131250   262500     2

The fields in the GROUP BY clause let the query engine know across what span it will do the aggregate functions. You don't get one row with 16000 for the Lowest... you get one row per unique combination of values in the fields in the group by clause. Since there is only one field in it, you get as many rows as there are unique Depts in the Salaries table.

If instead, you just want the values for everything in the table, you have to remove the departments from the SELECT and GROUP BY clauses:

SELECT
      Lowest = Min(Salary),
      Highest = Max(Salary),
      Average = Avg(Salary),
      DeptCost = Sum(Salary),
      Employees = Count(*)
   FROM Salaries

---- Result set:
Lowest  Highest  Average  DeptCost  Headcount
 16000  135000    65000   520000     8

Note that if you include the EmpID in the SELECT and GROUP BY clauses, you get something not very spectacular:

SELECT
      EmpID,
      Dept,
      Lowest = Min(Salary),
      Highest = Max(Salary),
      Average = Avg(Salary),
      DeptCost = Sum(Salary),
      Employees = Count(*)
   FROM Salaries
   GROUP BY
      EmpID,
      Dept

---- Result set:
EmpID Dept  Lowest  Highest  Average  DeptCost  Headcount
1      1     45000   45000    45000    45000     1
2      1     90000   90000    90000    90000     1
3      1     60000   60000    60000    60000     1
4      2     16000   16000    16000    16000     1
5      2     24000   24000    24000    24000     1
6      2     22500   22500    22500    22500     1
7      3    135000  135000   135000   135000     1
8      3    127500  127500   127500   127500     1

You get one row for each unique combination of EmpID and Dept... and since each row in the source data is unique for these two fields, you get one row for each.

I hope this helps!

-------------------------------------
A sacrifice is harder when no one knows you've made it.

 

[ldejesuspr]

Thanks to you all guys! I was able to correct my query