Using COUNT Function On Multiple Columns With GROUP BY Restrictions
Mar 10, 2014
Consider the following dataset:
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.
I have a database that contains a column for UnitName , BeginDate andEndDate.I want to pass two parameters (@BeginDate and @EndDate) and retrieve atable of valuesthat include UnitName along with Counts for each UnitName.SELECT UnitName, COUNT(BeginDate) AS Start(SELECT COUNT(EndDate) AS Finish WHERE EndDate BETWEEN @BeginDate AND@EndDate)FROM TableWHERE BeginDate BETWEEN @BeginDate AND @EndDateGROUP BY UnitNameORDER BY UnitNameThis works. But when I try to add another count by using a subselect Iget an error dealing with GROUP BY not including the column in mysubselect.How is the best way to Count two columns using Group By.
I'd like to first figure out the count of how many rows are not the Current Edition have the following:
Second I'd like to be able to select the primary key of all the rows involved
Third I'd like to select all the primary keys of just the rows not in the current edition
Not really sure how to describe this without making a dataset
CREATE TABLE [Project].[TestTable1]( [TestTable1_pk] [int] IDENTITY(1,1) NOT NULL, [Source_ID] [int] NOT NULL, [Edition_fk] [int] NOT NULL, [Key1_fk] [int] NOT NULL, [Key2_fk] [int] NOT NULL,
[Code] .....
Group by fails me because I only want the groups where the Edition_fk don't match...
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)
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 want to build query to return how many rows are in this query:select distinct c1, c2 from t1But SQL won't accept this syntax:select count (distinct c1, c2) from t1Does someone know how to count multiple distinct columns? Thanks.--Disclaimer: This post is solely an individual opinion and does not speak onbehalf of any organization.
I'm working on a data analysis involving a table with a large number of records (close to 2 million). I'm using only three of the columns in the table and basically am grouping results based on different criteria. The three columns are PWSID, Installation and AccountType. I have to Provide the PWSID column with a count of the total number of installations per PWSID, also a count of AccountTypes per PWSID. I have the following query, but the numbers aren't adding up and I'm not sure why. I'm falling short in the total count by around 60k records.
This is so complicated (for me) because I usually only work with single table and simple queries (SELECT, INSERT, UPDATE), but now I am in a situation where I am stuck.
What I am trying to archive is that: when a project manager logged-into his/her account, a grid-view will show a quick overview for all of his/her projects (id, created date, name and how many files are in pending) like below picture:
3 tables will be involved are:
Sample data for manager_id = 11
I tried this query but it not worked, it seems to display all columns right but the COUNT pending files column (assume the manager_id = 11)
SELECT COUNT(file_id) as 'Pending files', projects.project_id, projects.project_name, projects.status, projects.start_date FROM ((project_manager INNER JOIN files ON project_manager.mag_id = files.manager_id AND project_manager.mag_id = 11 AND file_status = 'Pending') INNER JOIN projects ON projects.project_id = project_manager.project_id) GROUP BY projects.project_id, projects.project_name, projects.status, projects.start_date ORDER BY projects.status, projects.start_date DESC
1 2015 ba1 137 HL EL Eco 2 2015 ba1 138 EL SL HS 3 2015 ba1 139 SL EL His
From this table i use to admit a student and select their choice of group simultaneously all the subjects associated with GROUP is save on another table.
Here is the TABLE 2 Structure and sample data:
table 2 (NAME - tblstudetail)
id studentID session course sub1 sub2 sub3
1 15120001 2015 ba1 EL SL HS 2 15120002 2015 ba1 HL EL Eco 3 15120003 2015 ba1 SL EL His 4 15120004 2015 ba1 HL EL Eco
AND so no..........................
Now i just want to COUNT the Number of Groups Filled in tblStudateil.
I am trying to convert the rows in a table to columns. I have found similar threads on the forum addressing this issue on a high level suggesting the use of cursors, PIVOT Transform, and other means. However, I would appreciate if someone can provide a concrete example in T-Sql for the following subset of my problem.
Consider that we have Product Category, Product and its monthly sales information retrieved as follows:
I have purposefully included QtySold here as I need to display both Quantity and Sales as measured column groups in my report. Can this be achieved in sql? I would appreciate any responses.
select CurrencyCode,TransactionCode,TransactionAmount,COUNT(TransactionCode) as [No. Of Trans] from TransactionDetails where CAST(CurrentTime as date)=CAST(GETDATE()as date) group by TransactionCode, CurrencyCode,TransactionAmount order by CurrencyCode
select CurrencyCode,TransactionCode,TransactionAmount,COUNT(TransactionCode) as [No. Of Trans] from TransactionDetails where CAST(CurrentTime as date)=CAST(GETDATE()as date) group by TransactionCode order by CurrencyCode
But of course this codes gives an error, but how can I get my desired result??
I am trying to figure out how to use the select maximum command in SQL Server 2005. I have already created a database and I have it populate it with multiple fields and multiple records. I Would like to create a new column or field which contains the maximum value from four of the fields. I have already created a column and I am trying to figure out how to use a command or SQL statement which is entered into the computed equation or formula in the properties for this field/column.
Any help you can provide will be greatly appreciated!
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.
set buyerset as exists(dimcustomer.leval02.allmembers,custoertypeisRetailers,"Sales") set saleset(buyerset) set custdimensionfilter as {custdimensionmemb1,custdimensionmemb2,custdimensionmemb3,custdimensionmemb4} set finalset as exists(salest,custdimensionfilter,"Sales") Set ProdIP as dimproduct.dimproduct.prod1 set Othersset as (cyears,ProdIP) (exists(([FINALSET],Othersset,dimension2.dimension2.item3),[DimCustomerBuyer].[ParentPostalCode].currentmember, "factsales")).count
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.
SELECT Node_ID,Day,Operation, AA,BB FROM (SELECT CASE WHEN Operation LIKE 'NOTIFY' THEN SUM(Total_request) ELSE 0 END AS AA, CASE WHEN OPERATION LIKE 'SEARCH' THEN SUM(Total_requests) ELSE 0 END AS BB,Node_ID,DAY,Operation
[code]....
So i want to make two columns by the name of operation. in the real code AA and BB are calculates with many counters. My code doesn't work, I have an error: "not a single-group group function" .....
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?