I have 4 archive tables and 1 active table that are created the same, but contain different data based on the date. I need to get results that have three columns: AuthorName, Month, Total. This is currently working, but through my research I can't find how to start going about dealing with the fact that each Author has some of his results from one month in one table and some in another table and how to add those together into one row. Example:
(What I'm Getting)
AuthorName Month Total
Test, Fred 3 43
Test, Fred 3 12
Test, Fred 2 56
Test, Fred 5 35
I have two tables one with current data and a second with the same design that holds purged history.
I was going to create view and then jsut use a where clause to filter both tables but I figured it would be faster if the where clause was passed into each query as opposed to the whoel view having to load and then be filtered.
Select PatientName, MemberId FROM CLAIMS WHERE MemberID = @MemberID UNION Select PatientName, MemberId FROM CLAIMSHistory WHERE MemberID = @MemberID
But it appears that if there are no records in the first statement that the whole thing fails. I tried the union all operator with out any luck either.
Now if had a view like this; Create view vAllClaims as Select PatientName, MemberId FROM CLAIMS UNION Select PatientName, MemberId FROM CLAIMSHistory
And said select * from vAllClaims where memberid = 12345 would the query optimizer put the build the where statement onto each subquery or pull all the data first and query it?
COL1 | COL2 | COL3 | COL4 1 | FD | DR. A | Y 2 | FD | DR. A | Y 3 | FD | DR. A | N 4 | FD | DR. A | Y 5 | FD | DR. A | Y 6 | PF | DR. A | Y 7 | FD | DR. B | Y 8 | PF | DR. B | N
Consider the script below:
SELECT COL2, COL3, COUNT(COL1) AS TOTALS FROM CASES GROUP BY COL2, COL3 ORDER BY COL3, COL2
The script above produces the following output:
COL2 | COL3 | TOTALS FD | DR. A | 5 PF | DR. A | 1 FD | DR. B | 1 PF | DR. B | 1
I need to add one more column to the script that counts records with 'Y' in COL4 for each COL1 category (FD, PF). The final dataset would look like this:
COL2 | COL3 | TOTALS | NEWCOL FD | DR. A | 5 | 4 PF | DR. A | 1 | 1 FD | DR. B | 1 | 1 PF | DR. B | 1 | 0
I am having a hard time trying to use COUNT() on multiple columns with the GROUP BY restrictions that exist.
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.
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)
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.
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;
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 !!!
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
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
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?
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
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.
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
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?
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:
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
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
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
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'
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.
I have three tables, say, A, B, and C. Both B and C contain/reference the primary key of A, ie B.a_id, and C.a_id. Multiple rows in B and multiple rows in C can have the same a_id. C and B have no relationships and essentially independant of each other.
I'm trying to find a single query (to prevent having to rewite function interfaces) that can return all the fields of A, the sum of a feilds in B, B.cost, where B.a_id = A.a_id and finially the count of C.a_id 's for where C.a_id = A.a_id.
The query I've been worlking on so far is as follows,
SELECT A.name, A.a_id, SUM (B.cost), COUNT ( DISTINCT C.a_id) FROM A LEFT JOIN B USING (a_id) LEFT JOIN C USING (a_id) GROUP BY A.a_id
This produces the correct result for the count of C.a_id (thanks to a DISTINCT) but the sum of B.cost is out by the factor of count C.a_id. I can see why this is happening but have completely run out of ideas. Group by on two columns may be? A strategically placed subquery? Is is even possible?
Any help would be much appreciated. Thanks in advance for your time.
Peter
PS I'm new to this forum thing so if you want the unsimplified query with all the full names just let me know and I'll post it.
Hi, I have two tables in my DB: tbl_Users: callSign(Char), FirstName(Char), LastName(Char) tbl_Events: CallSign(Char), TotalKM(Char), EventDate(SmallDateTime)... Plenty of others, but they're not relevnt.
The result that I want to see is:
CALL SIGN Last Name First Name Date Number Of Events TotalKM 111 MR. X 01/01/2008 3 40
I know this: The number of events is countable. if you do the following convert "Convert(int, TotalKM) you can sum up the Total KM.
Does anyone have any recommendations on how to solve the following?I would like to have a query that selects ALL columns from a database,however only records that have a count of a certain column which isgreater than 1 when the results are grouped by a few columns. I knowthe following query doesnt work (because it contains items in theSELECT that arent in the GROUP BY), but its the jist of what I need todo.select a,b,c,d,e,f,g,hfrom table1group by a,b,c,dhaving count(e) > 1Can anyone help me out with this?
I pulled data from several tables into temp table and I want to write a query to do count specfic data in certain columns and group it by the Owner... here is a sample table and query below, however in the real table there are more owner's, so I want to avoid using statements like - where owner = 'Smith'
Query ------ Select distinct(owner), (Select count(Color) from #table1 where Color = 'Blue') Blue, (Select count(Color) from #table1 where Color = 'Green') Green, (Select count(Color) from #table1 where Color = 'Red') Red, (Select count(Data) from #table1 where Data = 'A')A, (Select count(Data) from #table1 where Data = 'B')B, (Select count(Data) from #table1 where Data = 'C')C, (Select count(nextaction) from #table1 where nextaction = 'Level1')Level1, (Select count(nextaction) from #table1 where nextaction = 'Level2')Level2, (Select count(nextaction) from #table1 where nextaction = 'Level3')Level3, from #table1 group by owner
Does't really work... I tried putting a group by in the sub select but I get and error because I get multiple rows and that is not allowed when you use a sub query as an expression... so it said..
I built a report with one field as a group. I want to count the number of rows in each field so I can add it to the group field or somw where in the report.
How can I count how many rows do I have in each group?
