Re-phrased W More Details (SQL Is Giving Different Row Counts)

Apr 21, 2004

Hi,

...giving a very 'summarized' scenario of the problem I have trying to
solve all day (make it 2 days now).

Below are the relevant DDLs... I am not listing the DDLs of my other tables:

CREATE TABLE [SalesFACT] (
[varchar] (10),
[TransDate] [varchar] (10),
[SaleAmt] [float],
[CustCode] [varchar] (10)
. . .
)

I populate the above table via a DTS and have checked and have verified that correct data is coming in... I also have a product master table; for business reasons we can have the same product created with different ProductCodes though the rest of the Product details are EXACTLY the same. We have covered this using a field named 'UniqueProdCode'.

CREATE TABLE ProdMaster(
[ProdCode] [varchar] (10),
[ProdName] [varchar] (35),

[UniqueProdCode] [varchar] (10),

... many other product fields e.g. unit price, category etc...
...
)

First a small Request:
Please note that I have NOT defined links between my tables (in the diagram editor) nor have I defined Primary keys (or any constraint) for any of the tables. When you kindly reply, please suggest I should define primary keys for the tables and also link them in the diagram editor.


[u]THE PROBLEM:
When I do a count(*) query on the table 'SalesFACT', I get the correct number of records.

If I create a view, add table 'SalesFACT' and table ProdMaster, link the
UniqueProdCode field of table 'SalesFACT' with the UniqueProdCode field of ProdMaster (so that I can also get the name, category, etc. for the products in the SalesFACT), and run a count(*) query I get a much higher and incorrect number of rows. The SQL for the view is:


SELECT dbo.SalesFACT.TransDate, dbo.SalesFACT.UniqueProdCode,
dbo.SalesFACT.SaleAmt
FROM dbo.SalesFACT INNER JOIN dbo.ProdMaster ON dbo.SalesFACT.UniqueProdCode = dbo.ProdMaster.UniqueProdCode


Kindly note that I have checked and the contents of the table SalesFACT' UniqueProdCode field DOES contain the correct data i.e. it contains the UniqueProdCode and NOT the ProdCode.

But if i link the "wrong fields", I get the correct count count :confused: i.e. I create a very similar view (as mentioned above) but instead link the UniqueProdCode of table SalesFACT with the ProdCode field (not the UniqueProdCode field) of ProdMaster
table I get the correct count. This is really driving me nuts and I just can't understand what's going on. For your convenience here is the SQL for the 2nd view:


SELECTdbo.SalesFACT.TransDate, dbo.SalesFACT.UniqueProdCode,
dbo.SalesFACT.SaleAmt
FROM dbo.SalesFACT INNER JOIN dbo.ProdMaster ON dbo.SalesFACT.UniqueProdCode = dbo.ProdMaster.ProdCode


Please guide... I have run out of all the things that I could check and thus this SOS and F1

Billions of thansk in advance.

View 1 Replies


ADVERTISEMENT

SQL Is Giving Different Row Counts

Apr 20, 2004

Hi,

...giving a very 'summarized' scenario of the problem I have trying to
solve all day (make it 2 days now).

Below are the relevant DDLs... I am not listing the DDLs of my other tables:

CREATE TABLE [SalesFACT] (
[varchar] (10),
[TransDate] [varchar] (10),
[SaleAmt] [float],
[CustCode] [varchar] (10)
. . .
)

I populate the above table via a DTS and have checked and have verified that correct data is coming in... I also have a product master table; for business reasons we can have the same product created with different ProductCodes though the rest of the Product details are EXACTLY the same. We have covered this using a field named 'UniqueProdCode'.

CREATE TABLE ProdMaster(
[ProdCode] [varchar] (10),
[ProdName] [varchar] (35),

[UniqueProdCode] [varchar] (10),

... many other product fields e.g. unit price, category etc...
...
)

First a small Request:
Please note that I have NOT defined links between my tables (in the diagram editor) nor have I defined Primary keys (or any constraint) for any of the tables. When you kindly reply, please suggest I should define primary keys for the tables and also link them in the diagram editor.


[u]THE PROBLEM:
When I do a count(*) query on the table 'SalesFACT', I get the correct number of records.

If I create a view, add table 'SalesFACT' and table ProdMaster, link the
UniqueProdCode field of table 'SalesFACT' with the UniqueProdCode field of ProdMaster (so that I can also get the name, category, etc. for the products in the SalesFACT), and run a count(*) query I get a much higher and incorrect number of rows. The SQL for the view is:


SELECT dbo.SalesFACT.TransDate, dbo.SalesFACT.UniqueProdCode,
dbo.SalesFACT.SaleAmt
FROM dbo.SalesFACT INNER JOIN dbo.ProdMaster ON dbo.SalesFACT.UniqueProdCode = dbo.ProdMaster.UniqueProdCode


Kindly note that I have checked and the contents of the table SalesFACT' UniqueProdCode field DOES contain the correct data i.e. it contains the UniqueProdCode and NOT the ProdCode.

But if i link the "wrong fields", I get the correct count count :confused: i.e. I create a very similar view (as mentioned above) but instead link the UniqueProdCode of table SalesFACT with the ProdCode field (not the UniqueProdCode field) of ProdMaster
table I get the correct count. This is really driving me nuts and I just can't understand what's going on and why the "REVERSE" logic. For your convenience here is the SQL for the 2nd view:


SELECTdbo.SalesFACT.TransDate, dbo.SalesFACT.UniqueProdCode,
dbo.SalesFACT.SaleAmt
FROM dbo.SalesFACT INNER JOIN dbo.ProdMaster ON dbo.SalesFACT.UniqueProdCode = dbo.ProdMaster.ProdCode


Please guide... I have run out of all the things that I could check and thus this SOS and F1

Billions of thansk in advance.

View 2 Replies View Related

Row Counts

Feb 5, 2007

If I right click and browse the properties for the table I can get the value of rows. But for the same table if I do select count(*) from table the value does not match the table properties rows. Please can some one tell me why this is so?

SQL Newbie

View 2 Replies View Related

Counts By Groups

Jan 8, 2007

I expect to get a record below with a count of 0 (and I do), but when I take the comments out (--) of lines 1 & 6 I don't understand why I get no records at all. I need to be able to see all teams in EvalAnswers even if none of the records satisfies the where clause.1 select Count(*) as cnt--, TeamID
2 from EvalAnswers
3 where CoID=@CoID
4 and EvaluatorID=@EvaluatorID
5 and (Scr0=0 and Sugg0 is NULL)
6 --group by TeamID
7

View 4 Replies View Related

Need Help Bracketing Counts

Sep 6, 2007

I need to create a view that shows the number of times that clients made payments, how many clients, and how much they paid over a period of time. I'm not sure if I can use a case for this. How can I put something like this together?
I have a tblClients with a clientid field
I have a tblPayments with the clientid, pmtdate, and pmtamount
For example:
1 Payment ----- 23 Clients ----- $16000
2 Payments ----- 12 Clients ----- $32000
3 Payments ----- 4 Clients ----- $13000
etc...

View 3 Replies View Related

GETTING ROW COUNTS FROM DATABASE

Nov 15, 2000

I need a procedure that will go to a database and give me all the row counts for the user tables.

Does anyone know how I can get this?

Thanks,
Dianne

View 3 Replies View Related

How To Do Multiple Counts

Dec 12, 2006

I just inherited an app, where I have two tables that look like this:
[Owners]
--------
Owner
...

[Cases]
-------
Owner
Status
Assigned
...

I need a query to get results that look like this:
[Results]
--------
Owner
# of cases records where Status='Open'
# of cases records Where Status='Pending'
# of cases records WHERE Status<>'Closed' AND Assigned=''

I have one query that works already, but it's using several nested selects. I know I ought to be able to do this using group by instead, and I like to know how.

View 4 Replies View Related

Multiple Counts

Jan 24, 2006

I am creating a database for a soccer league.

I would like to write a query that would give me results in a league table form .

How could I combine different count queries such as:

select hometeam, count(*) as homegames from matches where comp="en1pp" group by hometeam
order by hometeam

select hometeam, count(*) as homewins from matches where homescore>awayscore group by hometeam order by hometeam

into one query giving a three column result homteams, homegames, homewins.

Thanks

View 6 Replies View Related

Do Lots Of COUNTs

Sep 19, 2006

Hello :)

I seem to have somehow got myself into a situation where I'm having to run the following SELECTs, one after another, on a single ASP page. This is not tidy. How can I join them all together so I get a single recordset returned with all my stats in different columns?

SELECT COUNT(*) FROM tblQuiz WHERE [q3] = '5 years +' OR [q3] = '2 - 4 years'
SELECT COUNT(*) FROM tblQuiz WHERE [q4] <> '' AND [q4] IS NOT NULL
SELECT COUNT(*) FROM tblQuiz WHERE [q5] = 'Unhappy'
SELECT COUNT(*) FROM tblQuiz WHERE [q6] = 'Yes'
SELECT COUNT(*) FROM tblQuiz WHERE [q7] = 'Yes'
SELECT COUNT(*) FROM tblQuiz WHERE [q8] <> '' AND [q8] IS NOT NULL

View 8 Replies View Related

Compare Row Counts

Oct 6, 2006

Hi all,

I have a need to compare the number of rows returned from table A when it is joined to table B to the number of rows returned when there are no joins involved. If the number of rows returned are the same, then I need to proceed to execute my next step else end.

So, If RowCount A = RowCount A when A joined to B
THEN Goto Next Step
Else End

I need to put the above logic in a sp that I want to execute using a job.

Help is appreciated.

V

View 5 Replies View Related

Counts On Subquerys

Apr 22, 2004

OK I have a query that bring back a unique identifier and text value
for instance

Select Distinct global_id, Page_url from PageList

global_id page_url
---------- ---------------------------------
4306549 /true/attitudes.htm
1460753 /true/current/answerthis.htm
4303667 /true/current/answerthis.htm
4306549 /true/current/answerthis.htm

Now I want to do a count of the page_url.
Can this be done in one SQL statement using a sub query of some kind

View 6 Replies View Related

Mixing Counts, Min Or Max ?

Nov 28, 2007

Hi I have this

select emailid,
count(emailid) as 'No.of occurences',

FROM tableA
where start_moment between '2007-11-01' and '2007-11-02'
GROUP BY emailid
having (COUNT(emailid) > 1)



Fair enough this returns the emailid along with the amount of times it appears (all greater then 1 .. duplicated in other words)

My question is , there is also a start_moment field in tableA
so i need to get the max or min start_moment along with the above result?

View 3 Replies View Related

Retention Counts

Dec 4, 2007

I have members in a database who have paid thru dates. I am creating retention reports

I created a cross tab in Crystal (using SQL) that counts records that paid within a certain year. I need to create a script that will let me find when members skip payment for a year. Any ideas?

I was thinking of running a count of all paid (Activity) records, but still kind of stuck.

DZ

View 9 Replies View Related

Getting Table Counts

Jul 20, 2005

I want to get a resultset of every table in the database, with thecurrent record count of each. What is the easiest way to do this?I can get the list of tables with:Select s.name from sysobjects s where xtype = 'U'each s.name is a table name, but I'm not sure how to join a record countcolumn to the resultset.Thanks,RickN

View 4 Replies View Related

Dividing Counts

Sep 19, 2007

I'm sorry if someone has already posted this but I've looked through a few pages to see if someone had already posted and I couldn't find anything. Anyways, I have two counts and I would like to divide them to get a percentage. Basically I would like to see a percentage of how many tickets are overdue. Here's my SQL:

select count(*)[No. of Tickets Overdue],
case
when sum(datepart(dd,getdate() - j.report_date))>= pt.due_hours then 'Over Due'
when sum(datepart(dd,getdate() - j.report_date))>= pt.due_hours then 'Over Due'
when sum(datepart(dd,getdate() - j.report_date))>= pt.due_hours then 'Over Due'
when sum(datepart(dd,getdate() - j.report_date))>= pt.due_hours then 'Over Due'
end [Ticket Status]
from whd.priority_type pt
inner join whd.job_ticket j on pt.priority_type_id = j.priority_type_id
where j.status_type_id = '1' and j.deleted = '0' and not j.priority_type_id = '5' and not j.priority_type_id = '6'
group by pt.due_hours
order by pt.due_hours desc
COMPUTE SUM(count(*))

select count(*)[Count2]
from whd.job_ticket jt
where jt.status_type_id = '1' and jt.deleted = '0'-- and not jt.priority_type_id = '5' and not jt.priority_type_id = '6'
--COMPUTE [No. of Tickets Overdue]/[Count2]

I know this isn't correct but basically the commented line at the bottom is what I want to do. I've only been doing SQL statements for a few months now, so I know its novice but any help is appreciated.
Thanks in advance.

View 7 Replies View Related

Record Counts

Oct 8, 2006

Hi,

I have 10 databases created. each data base has arround 100 tables . i need to keep track of the number of records in each table of 10 databases and the last modification date on that table. The solution should be programatically by running a T-sql program or any stored procedures or any other but mechinical.

the output should specify the following:

Table #number_of_recs Last_date

database_name.Table_name ###,###,### DD/MM/YY



I will appreciate any assistance in this regard.

thanks,

View 1 Replies View Related

Can Anyone Help Me With This? It's Giving Me A Bad Headache!

Mar 9, 2005

Hi All,

I have a table called Prizes. Here's how it looks in design view with some value placed inside for Illustration purposes.

PrizeID 1, 2, 3, 4, 5
PromotionID 1, 1, 1, 2, 1
PrizeName 10 Cash, 5 cash, 10 cash, 15 cash, 20 cash

My challenge is that I need to write a stored procedure for example, that will find the PrizeID associated with the 4th count of the PromotionID that equals 1 . So in this example, counting to the 4th PromotionID that equalls 1 give us a PrizeID of 5.

I hope I've made myself clear! Can anyone write out a mini SP on how to do this.

Many many thanks in advance,
Brad

View 2 Replies View Related

Giving Permissions

May 10, 1999

Hi friends,

How can I give permission to a new user to all the tables in the Database.

I usually create New User and then give permission to each table One By One which takes lot of time.

regards,
zak.

View 2 Replies View Related

Sp Giving Error

Sep 13, 2006

i have to get the maximum into a output parameter. its giving error. whats the problem with this code

SET @supplier_code as EXECUTE (SELECT MAX(supplier_code)+1 AS Supp_Id FROM supplier)

suji

View 5 Replies View Related

Giving Up On This Now: RsLogonFailed

Feb 13, 2008

Hi there mates....


I have had my runnings with Vista and SSRS. I eventually got the reports server website to work by ways of lots and lots of butchering:



Making sure the RSWebApplication has got the following in:

<ReportServerUrl>http://m_vdberg_vista/ReportServer</ReportServerUrl> while having a blank in

<ReportServerVirtualDirectory></ReportServerVirtualDirectory>



Making sure that the reportserver's web.config doesn't have an xmlns or something tag in...



Pulling out hair



Pulling out some more hair



Eventually deciding to set the reports server and manager on the Default Web's application pool to make use of Classic .Net application pool.
It worked and I could finally browse my reports but every single one of them cannot be viewed and bombs out with:

Logon failed. (rsLogonFailed)


Logon failure: unknown user name or bad password. (Exception from HRESULT: 0x8007052E)














I have tried all sorts of combinations but none work..even Integrated Windows Authentication with the DB on my local machine. I have triple checked passwords etc and the SSRS configuration shows all of the statuses as green when i connect. Is there some sort of special setting that I have to set somewhere on Vista?? I have Vista Business

Cheers
Mike

View 6 Replies View Related

Multiple Counts In A Query

May 20, 2008

I have a table that is linked to other tables in one to many relationship.I have a query using LEFT OUTER JOINs to join the tables together.There are multiple counts in the query and count numbers are messed up.(if there are 4 records from one table and 3 from the other  - it shows 12 as the count)  Your help is appreciated. 

View 2 Replies View Related

Multiple Counts In A Sql Statement

Jan 26, 2004

How would I create a sql statement with 3 or 4 counts which would represent 3 or 4 different columns in a datagrid?

For example

SQL = "SELECT Count(department_id) as "totals1" FROM nonconformance WHERE department_id = '1'"


How would I make additional counts in this SQL statement that looks for when department_id=2 and 3 etc....

Thanks

View 7 Replies View Related

SQL Query For Multiple Counts

Dec 8, 1999

I need to build a table to combine data into a single table but I need it to include a count on more than 1 column. For example, I have a table containing a store number, an isbn, an on hand quantity, and an on order quantity. It's probably easier to explain with an example. I have a source table containing this:

store isbn OnHand OnOrder
===== ========== ====== =======
104 0394572368 2 0
108 0394572368 0 1
109 0394572368 2 0
104 3321695545 2 1
108 3321695545 1 0
109 3321695545 3 1

And I need a table that will combine the isbns and give me sums
and counts like this:

Isbn OnHandLocations OnHandQty OnOrderLocations OnOrderQty
========== =============== ========= ================ ==========
0394572368 2 4 1 1
3321695545 3 6 2 2

Does anyone know of a query that can accomplish this? Thanks in advance.

View 1 Replies View Related

Script For Table Counts

Feb 13, 2006

I need a generic script that will query every table (minus system tables) within a database and generate record counts for each table.

Anybody have a script that will do this ???

Thanks in advance,
Nancy

View 6 Replies View Related

2 Grouped Counts On 1 Table

Oct 10, 2007

I am trying to get a count of a job received date and a job closed date from the same table. I need these counts to be grouped by which team they are for. This is what I have and it isn't working:

SELECT HEAT.dbo.Profile.PrimaryTeamName,
COUNT(CallLog1.RecvdDate) AS OpenCalls,
COUNT(CallLog2.ClosedDate) AS ClosedCalls
FROM HEAT.dbo.Profile,
HEAT.dbo.CallLog CallLog1,
HEAT.dbo.CallLog CallLog2
WHERE HEAT.dbo.Profile.CustID = CallLog1.CustID AND
HEAT.dbo.Profile.CustID = CallLog2.CustID AND
CallLog1.CallID = CallLog2.CallID AND
((HEAT.dbo.Profile.PrimarySupportGroupID = 'ATS') OR
(HEAT.dbo.Profile.PrimarySupportGroupID = 'ats'))
GROUP BY HEAT.dbo.Profile.PrimaryTeamName,
CallLog1.RecvdDate,
CallLog2.ClosedDate
HAVING (CallLog1.RecvdDate = CONVERT([VARCHAR](10), GETDATE(), 120)) OR
(CallLog2.ClosedDate = CONVERT([VARCHAR](10), GETDATE(), 120))

I can get both counts to work individually, but as soon as I try to get them to go together I get some very interesting returns. I am drawing a complete blank as to what to do. Any info would be very helpful.

Thanks

View 5 Replies View Related

Performance Issue With Qty Counts By Day

May 16, 2007

I have an issue with the performance of the below script. I was under the impression that 1 select query would be fast than a while loop, but right now, that isn't the case... I'm on sql 2005 sp2, but the query plan is mostly the same on sql 2000 sp4 as well.

I've tried as best as possible to reproduce my situation with the below script. The data is limited to 2000 rows in CustomerDates, but in our environment, there are over 2million rows of customer activity. I want to summarize the Quantity owned per day, even if there was no activity.

Even though the below script is a small subset of data, the query plan given for it is the same as when there is a full set of data. The biggest hog of the execution is the Hash Match (Aggregate) section, being somewhere around 2-6 cost for this small set of data. That number times 1000 makes for a pretty massive query cost.

Does anyone have some ideas of what could be done to speed up the last query (there are 2 copies of it - each one using a different index).



SQL Code:






Original
- SQL Code




CREATE TABLE Integers
(
i int PRIMARY KEY
)

insert into Integers
values (0)
insert into Integers
values (1)
insert into Integers
values (2)
insert into Integers
values (3)
insert into Integers
values (4)
insert into Integers
values (5)
insert into Integers
values (6)
insert into Integers
values (7)
insert into Integers
values (8)
insert into Integers
values (9)


SELECT DATEADD(DAY, dt.i, '12-1-2006') as dtDate
INTO Dates
FROM
(
SELECT ones.i + tens.i * 10 + hundreds.i * 100 as i
FROM integers ones
CROSS JOIN integers tens
CROSS JOIN integers hundreds
) dt
WHERE dt.i < 150--limit the data to extend only into April 2007

CREATE UNIQUE CLUSTERED INDEX cnxDates ON Dates(dtDate)


SELECT CustomerID,
CASE WHEN CustomerID > 100 THEN ProductID ELSE 1 END as ProductID, --override productId of 1 to be 2 for some customers
CASE WHEN CustomerID > 100 OR ProductID = 1 THEN 10 ELSE -10 END as Qty,
CAST(CASE WHEN CustomerID > 100 OR ProductID = 1 THEN '1-1-2007' ELSE '3/1/2007' END AS datetime) as ActivityDate
into CustomerDates
from
(--make some customerIDs
select 1 + ones.i + tens.i * 10 + hundreds.i * 100 as CustomerID
from integers ones
cross join integers tens
cross join integers hundreds
) c
cross join
(
select i as ProductID
from integers dbl--double the records
where i IN (1, 2)
) p

CREATE UNIQUE CLUSTERED INDEX cnxUnique ON CustomerDates(CustomerID, ActivityDate, ProductID)

--make a copy of the table w/ a different style of index
SELECT * INTO alt_CustomerDates FROM CustomerDates
CREATE UNIQUE CLUSTERED INDEX cnxUnique ON alt_CustomerDates(ActivityDate, CustomerID, ProductID)



SELECT c.CustomerID, d.dtDate, SUM(c.Qty) AS quantity, c.ProductID
FROM Dates d
INNER JOIN CustomerDates c
ON c.ActivityDate <= d.dtDate
GROUP BY c.CustomerID, c.ProductID, d.dtDate
HAVING SUM(c.Qty) > 0

SELECT c.CustomerID, d.dtDate, SUM(c.Qty) AS quantity, c.ProductID
FROM Dates d
INNER JOIN alt_CustomerDates c
ON c.ActivityDate <= d.dtDate
GROUP BY c.CustomerID, c.ProductID, d.dtDate
HAVING SUM(c.Qty) > 0






CREATE TABLE Integers(    i int PRIMARY KEY)    INSERT INTO Integers   VALUES (0)   INSERT INTO Integers   VALUES (1)   INSERT INTO Integers   VALUES (2)   INSERT INTO Integers   VALUES (3)   INSERT INTO Integers   VALUES (4)   INSERT INTO Integers   VALUES (5)   INSERT INTO Integers   VALUES (6)   INSERT INTO Integers   VALUES (7)   INSERT INTO Integers   VALUES (8)   INSERT INTO Integers   VALUES (9)  SELECT DATEADD(DAY, dt.i, '12-1-2006') AS dtDateINTO DatesFROM   (   SELECT ones.i + tens.i * 10 + hundreds.i * 100 AS i   FROM integers ones      CROSS JOIN integers tens      CROSS JOIN integers hundreds   ) dtWHERE dt.i < 150--limit the data to extend only into April 2007 CREATE UNIQUE CLUSTERED INDEX cnxDates ON Dates(dtDate)  SELECT CustomerID,        CASE WHEN CustomerID > 100 THEN ProductID ELSE 1 END AS ProductID, --override productId of 1 to be 2 for some customers       CASE WHEN CustomerID > 100 OR ProductID = 1 THEN 10 ELSE -10 END AS Qty,       CAST(CASE WHEN CustomerID > 100 OR ProductID = 1 THEN '1-1-2007' ELSE '3/1/2007' END AS datetime) AS ActivityDateINTO CustomerDatesFROM   (--make some customerIDs   SELECT 1 + ones.i + tens.i * 10 + hundreds.i * 100 AS CustomerID   FROM integers ones      CROSS JOIN integers tens      CROSS JOIN integers hundreds   ) c   CROSS JOIN    (   SELECT i AS ProductID   FROM integers dbl--double the records   WHERE i IN (1, 2)   ) p CREATE UNIQUE CLUSTERED INDEX cnxUnique ON CustomerDates(CustomerID, ActivityDate, ProductID) --make a copy of the table w/ a different style of indexSELECT * INTO alt_CustomerDates FROM CustomerDatesCREATE UNIQUE CLUSTERED INDEX cnxUnique ON alt_CustomerDates(ActivityDate, CustomerID, ProductID)   SELECT c.CustomerID, d.dtDate, SUM(c.Qty) AS quantity, c.ProductIDFROM Dates d   INNER JOIN CustomerDates c      ON c.ActivityDate <= d.dtDateGROUP BY c.CustomerID, c.ProductID, d.dtDateHAVING SUM(c.Qty) > 0 SELECT c.CustomerID, d.dtDate, SUM(c.Qty) AS quantity, c.ProductIDFROM Dates d   INNER JOIN alt_CustomerDates c      ON c.ActivityDate <= d.dtDateGROUP BY c.CustomerID, c.ProductID, d.dtDateHAVING SUM(c.Qty) > 0

View 1 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

Duplicate Counts Per Column

Nov 6, 2006

I'm have a problem with trying to generate a view that has a count of duplicates values per column in a table.

example I have a table with the following structure:

CREATE TABLE [dbo].[TestDpln](
[CountryCode] [smallint] NOT NULL DEFAULT ((0)),
[NPA] [smallint] NOT NULL DEFAULT ((0)),
[NXX] [smallint] NOT NULL DEFAULT ((0)),
[XXXX] [smallint] NOT NULL DEFAULT ((0)),
[3-Digit] [nvarchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[4-Digit] [nvarchar](4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SiteIdx] [int] NULL DEFAULT ((0)),
CONSTRAINT [TestDpln$PrimaryKey] PRIMARY KEY NONCLUSTERED
(
[CountryCode] ASC,
[NPA] ASC,
[NXX] ASC,
[XXXX] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

the data loaded looks like this

INSERT INTO dbo.TestDpln VALUES('1','312','555','1212','212','1212','1')
INSERT INTO dbo.TestDpln VALUES('1','312','555','1213','213','1213','1')
INSERT INTO dbo.TestDpln VALUES('1','525','555','2212','212','2212','2')
INSERT INTO dbo.TestDpln VALUES('1','525','555','2213','213','2213','2')

the results I need are

SiteIdx 3 Digit Count 4 Digit Count
------- ------------- -------------
1 2 0
2 2 0

I've tried various queries the closest being:

SELECT DISTINCT SiteIdx, COUNT(*) as "3-Digit Count"
FROM dbo.TestDpln
GROUP BY SiteIdx, "3-Digit"
HAVING COUNT(*) > 1

but it only shows one column and one site I'm not sure how to get the '4 Digit Count' column to show up and the rest of the sites. below are the results I get so far.

SiteIdx 3 Digit Count
------- -------------
1 2

any help would be great.
Thanks
Mike

View 8 Replies View Related

CTE ResultSet Counts Vary

Jan 18, 2013

I have the below CTE that I just can't seem to get to give me the right results. Basically what im trying to do is use the first query to show the "sources" that are involved in each inquiry and the second query to show which of those have became "admissions" the thing is the counts of the sources when the CTESource query is ran alone is different than my query to join the two tables.

Code:
With CTESource(Total, ID, Source, Program) AS
(
SELECT count(Inquiry.ID) as Total, Referral.InquiryID_fk, Source, Inquirer.Program from Referral
Inner Join Inquiry on Inquiry.ID = Referral.InquiryID_fk
Inner Join Inquirer on Inquirer.ID = Inquiry.InquirerID_fk

[code]....

The total inquiries can be higher than the source totals since a source isnt required in the system as well as there does not have to be admissions regardless of inquiry count.

View 1 Replies View Related

T-SQL (SS2K8) :: How To Add Counts To A Query

Jun 11, 2014

How to get task counts in a sales report.

The data is located in three tables: Projects, ProjectTasks, Sales.

I need to group the data by month and project. It needs to include sales per month as well as the number of each project task completed that month.

Projects:
ProjectIDName
1Project1

ProjectTasks:
ProTaskIDProjectIDTaskCodeBeginDateEndDate
11 Task11/1/20141/15/2014
21Task21/15/20141/20/2014
31Task3 1/21/20141/29/2014

Sales:
SalesIDProjectIDClosingAmount
11 1/31/2014$5000

Query Output:
ClosingDateProject TotalSalesTask1sTask2sTask3s
1/2014Project1$500011 1

My query so far is:

SELECT right('0' + cast(month(s.closing) as varchar(2)), 2) + '/' + cast(year(s.closing) as varchar(4)) as ClosingDate,
p.name as Project, SUM(s.amount) as TotalSales
FROM Sales s
JOIN Project p ON p.projectID = s.projectID
WHEREs.closing >= DATEADD(mm, -12, GETDATE())
GROUP BY right('0' + cast(month(s.closing) as varchar(2)), 2) + '/' + cast(year(s.closing) as varchar(4)), p.name

This will give me the grouping by month/year and project.

View 4 Replies View Related

Multiple Counts And A Score

Feb 17, 2014

I have a table called enablers , with the following data

title Raiser Assignedto
book Fred John
Apple Peter Peter
Orange Bill Roger
Cup John Fred

For each time a users name appears in the raiser column they get 1 point, for each time a users name appears in the Assignedto column they get 1 point , but if their name appears in both Raiser and Assignedto for a particular row they only get 1 point not 2 points, I then need a count of raiser points plus a count of assignedto points to give a total points score ( raised plus assignedto)..I am looking how to get the output like below

Name Total Points
Fred 2
Peter 1
Bill 1
John 2
Roger 1

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

Compare Counts [SOLVED]

Mar 12, 2008

Hi, hope someone can help. I have two tables

Test1 that lists all the training courses that i can count to find out the total as below.

select count (distinct Training_Course) as total
from Test1

Then Test2 lists all our customers and courses they have attended. I count the courses attended and then group by their ID.

select Cust_id, count (Attended) as TotalAttend
from Test2
Group by Cust_id

What i am now trying to do, without any luck, is find out which Customers have attended all training sessions by comparing the two queries and only bringing back the cust_id where it matches the total count from the Test1 query.

Make any sense? Any help/suggestions gratefully recieved.

View 4 Replies View Related







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