Why Counts Multipled For 'Select.. Count And Group With Rollup?

Apr 10, 2008

I have been working on this 'Select Count' problem for the past week.  Somehow, the Counts were multipled.
2 requests showed 4 counts, 3 requests showed 9 counts, 4 requests showed 16 counts. The main question,
to the best of my knowledge, is the coding for DBadapter.Fill method.   After 'Left Join' 2 tables, how do I
code the DataTable names?   I have tried using TransDS.Tables("Table name") to solve the problem. But
the counts still showed mulpile #. 

Another question: why there are 1 '0' line and 2 'rollup' lines? 
 




Examiner
Requested
Scheduled
Finished
noShow
Cancelled
Deleted

 
0
0
0
0
0
0

 
312
249
97
60
39
45

 
255
210
90
60
15
30

 aaaa
4
4
0
0
0
0

 bbbb
9
9
3
0
6
0

 
16
16
0
0
16
0

 
16
4
2
0
2
12

 
4
4
2
0
0
0

  strApptsSQL = "SELECT c.LName + ', ' + c.FName AS Examiner, " & _

"COUNT(Case TransCode WHEN 'R' THEN 1 ELSE NULL END) AS Requested, " & _"COUNT(Case TransCode WHEN 'B' THEN 1 ELSE NULL END) AS Scheduled, " & _

"COUNT(Case TransCode WHEN 'F' THEN 1 ELSE NULL END) AS Finished, " & _"COUNT(Case TransCode WHEN 'W' THEN 1 ELSE NULL END) AS DSnoShow, " & _

"COUNT(Case TransCode WHEN 'D' THEN 1 ELSE NULL END) AS Deleted, " & _"COUNT(Case TransCode WHEN 'E' THEN 1 ELSE NULL END) AS Cancelled " & _

"FROM Sssss.dbo.TransHistory AS T " & _"LEFT JOIN Sssss.dbo.Requests AS r ON T.TransUserID = r.RequestStaffIDFK " & " " & _

"LEFT JOIN Employee.dbo.Contacts AS c ON r.RequestStaffIDFK = c.ContactPK " & " " & _"GROUP BY c.LName + ', ' + c.FName WITH ROLLUP " & _

"ORDER BY c.LName + ', ' + c.FName"

'declare and create the data adaptersDim oDAAppts As New OleDbDataAdapter(strApptsSQL, oConnect)

Try

' fill the Dataset using the data adaptersoDAAppts.Fill(TransDS, "Requests")

Catch oErr As Exception

' display error message in page

lblErr.Text &= oErr.Message & "<br />"

Finally

' be sure to close connection if error occurs

If oConnect.State <> ConnectionState.Closed Then

oConnect.Close()

End If

End Try

' bind DataGrid to tablegvExaminerAppts.DataSource = TransDS.Tables("Requests")

gvExaminerAppts.DataBind()

 

View 2 Replies


ADVERTISEMENT

SELECT COUNT Of MAX (GROUP BY)

Jul 16, 2006

Hello

I want to get the COUNT of


SELECT MAX(id) AS ids, Name, Version, Pack, Serial
FROM Products
GROUP BY Name, Version, Pack, Serial


SELECT COUNT(MAX(id) AS ids) AS countIds, Name, Version, Pack, Serial
FROM Products
GROUP BY Name, Version, Pack, Serial

doesnt work

thank you

View 4 Replies View Related

COUNT(DISTINCT) With ROLLUP

Aug 17, 2005

I'm struggling to fin a way to use DISTINCT keyword with ROLLUP (or Cube).For example,SELECT employee_city, employee_country, COUNT(DISTINCT employee_name)FROM employeeGROUP BY employee_city, employee_country WITH ROLLUPthat query does not work.Is there a workaround?Thx.

View 2 Replies View Related

Select Top N Results In A Group; Count(*)&<=n

Jan 18, 2003

Two tables: CompanyPrices(CompanyID, ProductID, Price), CompanyRegion(CompanyID, Region)
ProductID is the primary key.

I want to get 10 smallest prices in each Region. In other words, I am looking for 10 cheapest prices in each region. So, if there are 20 regions, I should get excatly 200 rows having prices for products from 200 companies if there were at least 10 companies in each region.

I tried the follwoing, but get incorrect results.

select S1.Region, S1.Price from (select CompanyPrices.*, Region from CompanyPrices inner join CompanyRegion on CompanyPrices.CompanyID=CompanyRegion.CompanyID) S1 inner join
(select CompanyPrices.*, Region from CompanyPrices inner join CompanyRegion on CompanyPrices.CompanyID=CompanyRegion.CompanyID) S2 on S1.Region = S2.Region
group by S1.Region, S1.Price having count(*)<=10 order by S1.Region, S1.Price

However, if I want to get 10 cheapest products for each company, the above sql works by modifying the join condition. Instead of S1.Region = S2.Region , I use S1.CompanyID = S2.CompanyID and I get correct results for 10 cheapest products for each company.

select S1.CompanyID, S1.Price from (select CompanyPrices.*, Region from CompanyPrices inner join CompanyRegion on CompanyPrices.CompanyID=CompanyRegion.CompanyID) S1 inner join
(select CompanyPrices.*, Region from CompanyPrices inner join CompanyRegion on CompanyPrices.CompanyID=CompanyRegion.CompanyID) S2 on S1.CompanyID = S2.CompanyID
group by S1.CompanyID, S1.Price having count(*)<=10 order by S1.CompanyID, S1.Price

I am not sure what is wrong in the first query and why it does not work when the second one works. Could someone help in making the first query work to give me correct results?

View 2 Replies View Related

Cube/Rollup Without Group By?

Jul 20, 2005

MS has been nice enough to add the Cube and Rollup operators so I canhave totals with my results.But out of shear meanness they won't let me use them unless I use aGroup By clause in my select. I have a proc with 25 fields, and haveno desire to Group By anything- when a certain field changes, I justwant a row with the total of that field.Is this possible?Thanks,Burt

View 2 Replies View Related

Transact SQL :: How To Select Columns That Are Not In GROUP BY And Get COUNT

Jul 3, 2015

I am using SQL 2012.  I have a GROUP BY and I want to select two other fields from my table at the same time: One column that is a string (account_code) and one that I need to perform a count on (customer_number).  I know the code COUNT(DISTINCT customer_number) works for getting that.   I need to select both of those fields on top of what I have.  I have the following:

DECLARE @Providers TABLE (ID INT IDENTITY(1,1),
Provider_Name VARCHAR(20),
Uniq_Id VARCHAR(10),
Total_Spent MONEY,
Total_Earned MONEY)
INSERT INTO @Providers (Provider_Name, Uniq_Id,Total_Spent,
Total_Earned)

[Code] .....

View 21 Replies View Related

Count Of Counts In SQL Server

Jun 9, 2006

Hi folks,

I have the following query to run:

select count(
select count(*)
from student
group by firstName
having count(*) > 1)

Basically, I first need to get a count of all students that have the same first name, then I need total count of all those students. How can I change the above query to get the result I need?

Thanks so much!

-Parul

View 8 Replies View Related

Cumulative Counts With GROUP BY?

Jun 6, 2014

I have the following:

SELECT '201305' AS PAYPERIOD,
EMPLOYEE,
RIGHT ('000' + CAST (DEPT_ID AS VARCHAR(3)) ,3) AS DEPARTMENT,
COUNT (EMPCODE) AS BONUSCOUNT_YTD
FROM Table1
WHERE (YEAR = 2013 AND PERIOD < 2)
GROUP BY EMPCODE, YEAR, PERIOD, DEPT_ID

[Code] ...

How can I get the counts to be cumulative? In other words, if an employee appears in pay period 201305 that's 1, if they then appear in pay period 201306 that becomes 2.

View 4 Replies View Related

Problem With Group Totals And Counts

Sep 21, 2007

I am having trouble in SRS determining the distinct count and total for a very complex report.
Basically my dataset return 234 rows. In my report I am using a list (I have to use a list instead of a table for exporting reasons) and I am grouping by accountID.
If I do a CountDistinct(Fields!accountid.value) I still get 234. It's almost like it's not taking in the filter of the group.
I can do a RunningValue for each value and I see it count from 1-23. So I know that there are only 23 values being diplayed.
I also tried doing a CountDistinct(Fields!accountid.Value,"groupname") and I still get the total dataset.

Finally I do not want to do the counting on the dataset (meaning the in the query) because I want the flexibility to use filters for conditional reporting. (I have multiple scenarios in which I need to view the data).

View 8 Replies View Related

Reporting Services :: SSRS Record Counts For Group Item

Aug 11, 2015

I have a table where I am grouping on one field and would like an individual (separate) count of values from another field of same table.  So for example, I have following data:

instance_id,  area,        values
101              North       1
102              North       2
103             East          2
104             East          2

I would like to report on Area, and count of rows with different Values types, for example:

Area                            Value - 1,    Value - 2,  Value - 3
North                                 1               1              0
East                                   0             2                0

I am not sure what the technical term is for such report, but I can group by Area column, and but its aggregating counts on different Value types that I am having difficulty in performing in SSRS.

View 2 Replies View Related

SQL 2012 :: Fast Way To Do Group By Count On Items Within Group?

May 1, 2014

select top 15 count(*) as cnt, state from table
group by state
order by cnt desc

[code[...

Can the above three queries be combined into one and still be fast, if so how?What i am trying to go is an item count, by group, similar to ones Inbox in Outlook.

View 9 Replies View Related

Total Of Your Counts In Select Queries

Mar 19, 2007

I wrote a simple select query that counts the number of records I have in certain zip codes. How can I get a total of the "count" column at the bottom of the results? For example, my results may look like this:

ZIP | (no column name for "count")
_____________________________________
89502 | 10
89509 | 15
89521 | 25

What statement would I use to get the total of '50' displayed in the resluts? Thank you in advance

-Lance

View 6 Replies View Related

Adding A Group By Clause And Getting A Count Of A Group

Feb 6, 2008

HiI am new to SQL and am having a problem. I need to fix my query to do the following...2) get a total of the number of rows returned.
DECLARE @StartDate varchar(12)DECLARE @EndDate   varchar(12)DECLARE @Region    varchar(20)
SET @StartDate = '01/01/2002'SET @EndDate   = '12/31/2008'SET @Region    = 'Central'
SELECTA.createdon,A.casetypecodename,A.subjectidname,A.title,A.accountid,A.customerid,A.customeridname,B.new_Region,B.new_RegionName
FROM  dbo.FilteredIncident AINNER JOIN dbo.FilteredAccount B ON A.customerid = B.accountid
WHERE (A.createdon >=@StartDate  AND A.createdon <= @EndDate)AND   (B.new_RegionName = @Region)AND   (A.casetypecode = 2) 
 

View 1 Replies View Related

How?: Group By Date And Count Rows In Group

Jan 29, 2007

I'm new to MSSQL 2005 and want to get a summary of a log table. I want to count all the rows for each date based on a DATETIME field called 'post_date' that holds the date and time of each record's creation.

this is the best I can come up with:

Code:


SELECT
DISTINCT(LEFT(post_date,11)) AS post_date, COUNT(DISTINCT(LEFT(post_date,11))) AS total_posts
FROM log_directory_contacts
GROUP BY post_date



The results show each date but the count column ('total_posts') returns '1' for every row even when I know their are more than 1 record on that date.

What am I doing wrong? Thanks!

View 9 Replies View Related

Group By And Count

Dec 21, 2007

I want to know how to merge the following data.  I am using 4 queries below.  I was hoping to do it with 1 query.   Table1Dist     Fund     VAE    AOVAW   AOMD      CourtMD     JudgeCAC   AOCAC   CourtVAE   JudgeVAE   JudgeI want to join the following 3 queries:DcountAll                                                                                                   DcountAOSelect Dist, Count(Dist)as Count from Table1  GROUP BY Table1.Dist           Select Dist, Count(Dist) as Count from Table1 Where Dist='AO' GROUP BY Table1.DistDcountCourtSelect Dist, Count(Dist) as Count from Table1 Where Dist='Court' GROUP BY Table1.DistSELECT DCountAll.Dist, DCountAll.Count, DcountAO.Count AS AO, DcountCourt.CountFROM DcountCourt RIGHT JOIN (DcountAO RIGHT JOIN DCountAll ON DcountAO.Dist = DCountAll.Dist) ON DcountCourt.Dist = DCountAll.Dist;

View 2 Replies View Related

Help ... COUNT + GROUP BY

May 30, 2008

hi everyone, I have a table: Help
A               B    C05/01/2008 100 1 05/01/2008 100 205/01/2008 100 205/01/2008 200 1 05/01/2008 200 2 
SELECT A, COUNT(DISTINCT C) FROM help GROUP BY A 
Result:1> 05/01/2008 2I need:1> 05/01/2008 4Thanks !!!

View 4 Replies View Related

How To Count GROUP BY

Nov 3, 1998

TO all SQL Gurus,

The following statement returns let say 4 records -

SELECT title, price FROM table GROUP BY title

what is the correct syntax for the statement to return the count of 4 ?

if I put ->

SELECT count (*) FROM table GROUP BY title

it'll return 1.

Thanks,


Frank

View 4 Replies View Related

Help On Count And Group

May 23, 2008

Hi everybody I have this sql code

SELECT i.infid, i.infname, i.infcalled, p.pubinfid, p.pubpub, p.publang, p.pubcount, p.pubid, n.cdpldesc
FROM info AS i INNER JOIN
pubssubscribe AS p ON i.infid = p.pubinfid INNER JOIN
newpubs AS n ON p.pubid = n.pubid
WHERE (i.infcond IS NULL)AND (p.pubid BETWEEN 30 AND 33) AND (p.pubcount > 1) AND (NOT (p.publang = '.'))

there are many records where infid appears more than once because people could subscribe in different in different publicatons.
ex.

infid pubid
1 30
1 32
1 33
2 30

etc... I want to count the infid appearing once and group it with infid

thanks

View 7 Replies View Related

Sum Of COUNT In Group By

Mar 25, 2004

SELECT emp_id,COUNT(*)
FROM emp
GROUP BY empId

THe above query returns the values

empid COUNT
1 4
2 4
3 5

BUT i want sum of the count (13) in the same query. How to achieve this.

Thanks.

View 4 Replies View Related

Help ... COUNT &#043; GROUP BY

May 29, 2008

hi everyone,

I have a table: Help

A B C
05/01/2008 100 1
05/01/2008 100 2
05/01/2008 100 2
05/02/2008 200 1
05/02/2008 200 2

SELECT a, COUNT(c) FROM Help GROUP BY a

Result:

1> 05/01/2008 3
2> 05/02/2008 2

But I need grouping columns B and C so that the result was

1> 05/01/2008 2
2> 05/02/2008 2

Is it possible? How can I do?
Thanks.

View 5 Replies View Related

Count Of Group By?

Jul 29, 2013

I want to join two tables and count the rows on the second table as something is grouped by the first. To be more clear. I have vendors and open tracking numbers for orders they have shipped. I want to list the vendor information and to group by the vendor. However I also want to count how many open orders that vendor has - which is on a different table. I have this so far:

SELECT `companyName`
, `emailAddress`
, `ccAddress`
, `dailyMessages`
, (SELECT count(*) FROM `tracking` WHERE `pkgStatus`!='4') AS 'openTracking'
FROM `vendor`
LEFT OUTER JOIN `tracking`
ON `vendor`.`id` = `tracking`.`vendorID`
WHERE (SELECT count(*) FROM `tracking` WHERE `pkgStatus`!='4') > 0
GROUP BY `vendor`.`id`

The problem is that this code results in this table. Where openTracking is always equal to the total count, not distinct to that vendor's ID

View 3 Replies View Related

SQL COUNT, SUM AND GROUP BY

Apr 5, 2006

Hello
I'm putting together a football database and want to show how many games a player played in a season.

SELECT COUNT (PlayerFixture.FixtureID) FROM PlayerFixture WHERE PlayerFixture.PlayerID = '::PlayerID::' GROUP BY Season

However if a player doesn't play any games in a particular season the COUNT function ignores it and just returns values in seasons he has played. Is there any way i can get the COUNT function to return a "0" or "-" if a player didn't play any games that season?

Cheers

View 10 Replies View Related

Count GROUP

Mar 13, 2007

hi,
this sounds simple, but ive to idea how ..
1. how to count all records we found when there's group by?
SELECT ItemID, CustomLotNo, Ownership
FROM tblItemDetail
GROUP BY ItemID, CustomLotNo, Ownership
ORDER BY ItemID

2. how to print rows number for each record? like the Expr1 column
Expr1 ItemID CustomLotNo Ownership
1 A A01 INT1
2 C GPB01 JAB2MY

~~~Focus on problem, not solution~~~

View 12 Replies View Related

Sub Group Count

Apr 24, 2008

I am doing employee shift report. I am showing totals for the shift, day and store. Store is the main group, day is the sub group under store group and shift is the sub group under the day group. I want to calculate number of shift group records (Number of shifts) and use it in the store level group calculation.

How to do it?

Thanks,
Muniappan Kandasamy

View 1 Replies View Related

Group By Count * &&>1?

May 23, 2007

Can this be used to prevent the repetition of records displayed in a page?




Code Snippet

SELECT T_ProgramGuests, GuestName
FROM T_ProgramGuests
GROUP BY ProgramID, GuestName
HAVING (COUNT(*) > 1)

I'm trying to prevent names being repeated. I only want the name to show once followed by the next name and so on. But only once.

View 1 Replies View Related

Group By Having Count(*)

Jan 4, 2008

I would like to select data from a database using 'Select * " based on a value in a row (same column)being unique.
By that I mean that that data must not repeat again. Idealy I would like to set the number my self so rather then unique I could say

select the rows from the database only if the uniquevalue does not repeat more then x number of times.
eg
Value A
Value B
Value C
Value B
Value B
Value C

So if I wanted to set the uniquness to 1 then only row with Value A would be collected.
If I set the uniquenss to =<2 then I would get data from rows with value A and C so
3 rows returned.

I have this so far

SELECT *
FROM SingleS.mdb
GROUP BY Uniquevalue
HAVING count(*) = 1

View 7 Replies View Related

Help ... COUNT + GROUP BY

May 30, 2008

Hi everyone,

I have a table: Help

A B C
05/01/2008 100 1
05/01/2008 100 2
05/01/2008 100 2
05/01/2008 200 1
05/01/2008 200 2

SELECT A, COUNT(DISTINCT C) FROM help GROUP BY A

Result:

1> 05/01/2008 2

I need:

1> 05/01/2008 4

Thanks !!!

View 12 Replies View Related

COUNT &&amp; GROUP BY

Oct 25, 2007

Probably has been solved a million times, but here it is anyway:

Say I have a table with follwoing rows:

Site Appointment Maintenance Date
NY 13 10-25-2007
CA 14 10-29-2007
NY 18 10-25-2007
NJ NULL 10-26-2007

I need to perform a simple count showing the total (Maintenance Dates) for the next 2 days. Additionally, if a site is listed, but it doesn't have any (Maintenance Dates) for the next 2 days, it must be part of the report with the total as zero.

I can do
SELECT Site, COUNT(*)
FROM dbo.MyTable
GROUP BY Site
-- for brevity, assume GETDATE() is set to 12:00 AM, today
WHERE [Maintenance Date] >= GETDATE() AND [Maintenance Date] < DATEADD(dd, 2, GETDATE())

but this only lists NJ and NY, because they both have counts over the next days. How do I get CA in the list?

Thanks.

View 6 Replies View Related

Group By And Count Of Each Group

Oct 23, 2007

Hi,
I use group by myItem, that displays rows for each value of myItem.
I want at the end of each group for each specific myItem, I want an extra row to tell me the count of the rows for that group.

How do i do that please. Here s what I need, for example for this set of rows:


myItem colum2 colum3
711056 22662 Finances / -2291 -2479916
711056 22663 Finances / -2380 -2576255
711056 43428 Cda) -323 -349635
711056 43428 . -B-(Cda) -44 -47628
711057 44348 . -B-(Cda) -355 -384273
711057 47033 . -B-(Cda) -1278 -1383384

I need 2 extra rows, one at the end of the items 711056 telling me there are 4 rows for the item 711056 and one row at the very end telling me there are: 2 rows for the item: 711057

Thanks a lot.

View 6 Replies View Related

Count And Group By With DateTime!

Nov 19, 2006

Hi all,
I have a problem with my query which is suppose to select count posts group by the date, the query works but doesnt return the count as i want, i think the problem that the datetime column contains also Time in hours which ofcourse isnt same in all rows in same day, so i dont know what  to do, Here's my Query:
SELECT PostID, Date, COUNT(Date) AS 'TotalPosts' FROM Posts GROUP BY PostID, Date
Thank you for help

View 2 Replies View Related

Using A Count If Within A Group By SQL Statement?

May 21, 2008

I have the following SQL Statement:
SELECT     CONVERT(char(10), FixtureDate, 101) AS Date, COUNT(*) AS 'NumberOfRecords'FROM         tblFixturesGROUP BY CONVERT(char(10), FixtureDate, 101)
I want to add a new column called "need results".
This column needs to be count if a certain cell is NULL.
Count If HomeScore IS NULL
as well as grouping by date and counting the number of records. So the third column needs to count the number of records where homescore IS NULL

View 1 Replies View Related

Trying To Get The First Count Of A Group Of Rows

Dec 24, 2003

Hi, I am trying to get the first row of what might be a group of any size of rows within an id that may contain a specific field. For eg

Row A = NoteID 1, FK_JobID 1, UnRead

Row B = NoteID 2, FK_JobID 1, UnRead

Row C = NoteID 3, FK_JobID 1, UnRead

I need the sql to return to just one Job (row) even though the job as 3 UnRead fields. But its returning 3 because its only doing what I'm asking. What I need it to do is just get the one Job (row) where any of the notes = UnRead.

I tried using Top 1, but that will only ever return one row and since I need it to return more than one job (row) it won't work.

Heres my attempt

DECLARE @UserID INT

SET @UserID = 4


SELECT User_Notes.BeenRead, Master_Jobs.By_Who, Master_Jobs.Next_Action, Master_Jobs.Due_Time, Master_Jobs.Due_Date, Master_Jobs.Contact,
Master_Jobs.Job_Title, Master_Jobs.JobID
FROM User_Notes INNER JOIN
Note ON User_Notes.FK_UN_NoteID = Note.NoteID INNER JOIN
Master_Jobs ON Note.FK_JobID = Master_Jobs.JobID

WHERE Note.FK_UserID = User_Notes.FK_UN_UserID AND
BeenRead = 'UnRead'

Thanks in advance

View 6 Replies View Related

Help With Count And Group By Query Pls!!

Dec 6, 2006

ok, so i have this table "SOLD_PRODUCTS" with this columns:
idProduct (int), quantitySold (int)

so i want to know how many products i have sold.

i have this query

Code:

SELECT idProduct, COUNT(idProduct) AS Total
FROM SOLD_PRODUCTS
GROUP BY idProduct


and get this
74 5
75 2
79 1
etc etc etc

this works correctly, but it doesnt counts the quantity sold, so i changed the query to


Code:

SELECT idProduct, quantitySold, COUNT(idProduct) AS Total
FROM SOLD_PRODUCTS
GROUP BY idProduct


but it crashes, mssql says i must add quantitySold to the group by clause.

but if i put the query

Code:

SELECT idProduct, quantitySold, COUNT(idProduct) AS Total
FROM SOLD_PRODUCTS
GROUP BY idProduct,quantitySold



i dont get the expected data, the idProducts appear more than once, they are no longer grouped by, the results in the count(idProduct) are wrong (i am not sure what they are counting now)

so now i dont understand how i can make my query, all i want is a query where in one collumn i have the product id, and in the other the quantity sold (that would be the count of all the product ids found multiplied by the quantity sold)

so now i am completelly lost, now i dont know what to do with my query.

any help pls pls pls!!!!

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved