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.
View 2 Replies
ADVERTISEMENT
Nov 14, 2006
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.
View 1 Replies
View Related
Aug 27, 2014
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...
View 4 Replies
View Related
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
Dec 9, 2013
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
[code]....
View 4 Replies
View Related
Nov 2, 2006
OK I am having problems trying to figure out or if it is even possible to do this in MSSQL
Here is my records
1=New
2=Open
3=Closed
Table stats
ID, Status
1 1
2 1
3 1
4 2
5 2
6 3
So I am trying to count each of the status
So Total records should be 6, New 3, Open 2, Closed 1 records.
SELECT COUNT(*) FROM Stats
Thats about all I know on this, will give me the total. but is there a way to count the total and count individuals.
View 1 Replies
View Related
Jul 20, 2005
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.
View 3 Replies
View Related
Sep 2, 2015
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.
CREATE TABLE [dbo].[CATASTRO_PSWID_SHPMUNINEW](
[Installation] [numeric](38, 8) NULL,
[AccountType] [nvarchar](50) NULL,
[PWSID] [smallint] NULL,
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
[code]....
View 8 Replies
View Related
Jun 23, 2014
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
result of this query:
View 5 Replies
View Related
Jan 13, 2015
How to get the lowest U.Price along with FX and Rate.
ID-U.Price-FX-Rate
1280 19.1196 EUR 3.85
1280 46.2462 USD 3.63
1280 6.32 RM 1.00
Required output.
ID-U.Price-FX-Rate
1280 6.32 RM 1.00
View 4 Replies
View Related
May 4, 2015
There are two tables
TABLE 1 (NAME - Groupseats)
id session course groupcode sub1 sub2 sub3
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.
View 10 Replies
View Related
Aug 3, 2007
Hello All,
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:
CategoryID
ProductID
ProductName
Month
UnitPrice
QtySold
SalesAmount
1
1
Panel
Jan
5
10
50
1
1
Panel
Feb
5
15
75
1
1
Panel
Mar
5
20
100
1
2
Frame
Jan
10
30
300
1
2
Frame
Feb
10
25
250
1
2
Frame
Mar
10
20
200
1
3
Glass
Jan
20
10
200
1
3
Glass
Feb
20
20
400
1
3
Glass
Mar
20
30
600
I would like it to be converted into following result set:
CategoryID
ProductID
ProductName
UnitPrice
QtySold_Jan
SalesAmt_Jan
QtySold_Feb
SalesAmt_Feb
QtySold_Mar
SalesAmt_Mar
1
1
Panel
5
10
50
15
75
20
100
1
2
Frame
10
30
300
25
250
20
200
1
3
Glass
20
10
200
20
400
30
600
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.
Thanks.
View 1 Replies
View Related
Jun 17, 2015
I have a SQL query like this
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
As per this query I got the result like this
CurrencyCode TransactionCode TransactionAmount No.OfTrans
AED BNT 1 1
AED BNT 12 1
AED SCN 1 1
AED SNT 1 3
[Code] ....
But I wish to grt result as
CurrencyCode TransactionCode TransactionAmount No.OfTrans
AED BNT 13 2
AED SCN 1 1
AED SNT 11 7
AFN BPC 8 6
[Code] ....
I also tried this
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??
View 5 Replies
View Related
Aug 25, 2006
Hello,
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!
Thank you,
Nathan
View 17 Replies
View Related
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
May 25, 2015
below data,
Countery
parentid
CustomerSkId
sales
A
29097
29097
10
A
29465
29465
30
A
30492
30492
40
[code]....
Output
Countery
parentCount
A
8
B
3
c
3
in my count function,my code look like,
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
it will take 12 to 15 min to execute.
View 3 Replies
View Related
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
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
Feb 12, 2015
I have a code like this:
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" .....
View 1 Replies
View Related
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
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
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
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
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
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
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
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
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
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
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
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
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
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