Transact SQL :: How To Count Where Two Tables Multiple Columns Match

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


Transact SQL :: COUNT And SUM Of Multiple Columns

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.

[Installation] [numeric](38, 8) NULL,
[AccountType] [nvarchar](50) NULL,
[PWSID] [smallint] NULL,


View 8 Replies View Related

Transact SQL :: Multiple Inserts Different Columns And Tables Into One Table

Oct 12, 2015

I have a Problem with my SQL Statement.I try to insert different Columns from different Tables into one new Table. Unfortunately my Statement doesn't do this.

If object_ID(N'Bezeichnungen') is not NULL
   Drop table Bezeichnungen;
create table Bezeichnungen
 Artikelnummer nvarchar(18),
 Artikelbezeichnung nvarchar(80),
 Artikelgruppe nvarchar(13),

View 19 Replies View Related

T-SQL (SS2K8) :: Select Group On Multiple Columns When At Least One Of Non Grouped Columns Not Match

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

Transact SQL :: Adding Count Before And After A Specific Time Doesn't Match Total Number Of Records

Nov 19, 2015

If I just use a simple select statement, I find that I have 8286 records within a specified date range.

If I use the select statement to pull records that were created from 5pm and later and then add it to another select statement with records created before 5pm, I get a different count: 7521 + 756 = 8277

Is there something I am doing incorrectly in the following sql?

DECLARE @startdate date = '03-06-2015'
DECLARE @enddate date = '10-31-2015'
DECLARE @afterTime time = '17:00'
General_Count = (SELECT COUNT(*) as General FROM Unidata.CrumsTicket ct

[Code] ....

View 20 Replies View Related

Transact SQL :: Match Items Between Two Tables

Jul 6, 2015

I have a table variable @tbl with one column (user) with example Laura, Scott, and Kevin.

Then I have a table usergroups with two columns (groupid, user) with example:

1, Laura
1, Scott
2, Laura
2, Scott
2, Kevin
3, Laura
3, Kevin
3, Scott
3, Jim
4, Laura
4, Kevin
4, Scott

I want to find groupid 2 and 4 because they exactly match the content of @tbl.

View 8 Replies View Related

Using A Match Table To Store Multiple Columns For Parent Data

Mar 1, 2008

Sorry for the confusing subject. Here's what im doing:I have a table of products. Products have N categories andsubcategories. Right now its 4. But there could be more down theline so it needs to be extensible.So ive created a product table. Then a category table that has manycategories of products, of which a product can belong to N number ofthese categories. Finally a ProductCategory "match" table.This is pretty straigth forward. But im getting confused as to how towrite views/sprocs to pull out rows of products that list all theproducts categories as columns in a single query view.For example:lets say productId 1 is Cap'n Crunch cereal. It is in 3 categories:Cereal, Food for Kids, Crunchy food, and Boxed.So we have:Product----------------1 Capn CrunchCategories-----------------1 Cereal2 Food for Kids3 Crunchy food4 BoxedProductCategories------------------1 11 21 31 4How do I go about writing a query that returns a single result set fora view or data set (for use in a GridView control) where I would havethe following result:Product results---------------------------------ProductId ProductName Category 1 Category 2Category 3 Category N ...------------------------------------------------------------------------1 Capn Crunch Cereal Food for Kids Crunchy foodBoxedAm I just thinking about this all wrong? Sure seems like it.Cheers,Will

View 1 Replies View Related

Transact SQL :: Select And Parse Json Data From 2 Columns Into Multiple Columns In A Table?

Apr 29, 2015

I have a business need to create a report by query data from a MS SQL 2008 database and display the result to the users on a web page. The report initially has 6 columns of data and 2 out of 6 have JSON data so the users request to have those 2 JSON columns parse into 15 additional columns (first JSON column has 8 key/value pairs and the second JSON column has 7 key/value pairs). Here what I have done so far:

I found a table value function (fnSplitJson2) from this link [URL]. Using this function I can parse a column of JSON data into a table. So when I use the function above against the first column (with JSON data) in my query (with CROSS APPLY) I got the right data back the but I got 8 additional rows of each of the row in my table. The reason for this side effect is because the function returned a table of 8 row (8 key/value pairs) for each json string data that it parsed.

1. First question: How do I modify my current query (see below) so that for each row in my table i got back one row with 19 columns.


If updated my query (see below) and call the function twice within the CROSS APPLY clause I got this error: "The multi-part identifier "A.ITEM6" could be be bound.

2. My second question: How to i get around this error?

CROSS APPLY fnSplitJson2(A.ITEM5,NULL) B,  fnSplitJson2(A.ITEM6,NULL) C

I am using Microsoft SQL Server 2008 R2 version. Windows 7 desktop.

View 14 Replies View Related

Transact SQL :: Query To Convert Single Row Multiple Columns To Multiple Rows

Apr 21, 2015

I have a table with single row like below

 _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
Column0 | Column1 | Column2 | Column3 | Column4|
 - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Value0    | Value1    | Value2    | Value3    |  Value4  |

Am looking for a query to convert above table data to multiple rows having column name and its value in each row as shown below

_ _ _ _ _ _ _ _
Column0 | Value0
 _ _ _ _ _ _ _ _
Column1 | Value1
 _ _ _ _ _ _ _ _
Column2 | Value2
 _ _ _ _ _ _ _ _
Column3 | Value3
 _ _ _ _ _ _ _ _
Column4 | Value4
 _ _ _ _ _ _ _ _

View 6 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:

Provider_Name VARCHAR(20),
Uniq_Id VARCHAR(10),
Total_Spent MONEY,
Total_Earned MONEY)
INSERT INTO @Providers (Provider_Name, Uniq_Id,Total_Spent,

[Code] .....

View 21 Replies View Related

Transact SQL :: How To Get Distinct Count Of All Columns Of A Table

Dec 3, 2015

I have a table called Employee which have 6 columns. This table are having 1000 records. Now I want to know the distinct value count of all these 6 columns as well as normal count. like this:

ColumnName DistinctCount NormalCount
Id 1000 1000
Name 1000 1000
Phone 600 600
PINCode 200 1000
City 400 1000
Gender 2 1000

View 5 Replies View Related

Transact SQL :: How To Count Number Of Specific Columns Of Table

Jul 11, 2015

I have a table called Employees which has lots of columns but I only want to count some specific columns of this table.

i.e. EmployeeID: 001

week1: 40
week2: 24
week3: 24
week4: 39

This employee (001) has two weeks below 32. How do I use the COUNT statement to calculate that within these four weeks(columns), how many weeks(columns) have the value below 32?

View 3 Replies View Related


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

Count Multiple Distinct Columns

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

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:


The script above produces the following output:

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:

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

Transact SQL :: Optimize Count Distinct For Multiple Groups Of Records?

May 21, 2015

I have a CTE returning a recordset which contains a column SRC.  SRC is a number which I use later to get counts and sums for the records in a distinct list. 

declare@startdate date = '2014-04-01'
declare@enddate date = '2014-05-01'
; with SM as

[Code] ....

-- If Referral start date is between our requested dates

ref.Referral_Start_Date between @startdate and @enddate


-- Include referrals which started before our requested date, but are still active during our date range.

(ref.Referral_Start_Date < @startdate and (ref.Referral_End_Date > @startdate OR ref.Referral_End_Date IS NULL ))
INNER JOIN c_sdt s on s.Service_Delivery_Type_Id = u.Service_Delivery_Type_Id
AND s.Service_Delivery_Unit_Id = 200
count(distinct (case SRC when 91 then client_number else 0 end)) As Eligable_91,


View 5 Replies View Related

Count From Multiple Tables

Jan 31, 2005

I have 4 tables
One is a user table and the other three contain records for the users. They all have a USERNAME column
I would like to get a count of records for each table grouped by USERNAME

My output would be:

Thanks For the help!

View 2 Replies View Related

How To Count Across Multiple Tables In A DB?

Sep 12, 2007

Thank you in advance for your assitance. I am trying to write a query that will query multiple tables for the same column. All the tables have thsi column "szF11". I am wanting something similar to this:

Code Snippet
SELECT count(ulID)
FROM (dbo.F_ACCOU_Data UNION dbo.F_AGNCY_Data UNION dbo.F_APPEA_Data UNION etc.....)
WHERE szF11 = ' '

Note: ulID is the name of a column that every table has and szF11 is also in every table.

Pseudo Code: I want to count how many ulID's (if there is a row then something is in the ulID column it is never blank) in all the tables that are listed that have a blank in the szF11 column.

I am getting a very cryptic error message and of course I can't find anything in the documentation to help me understand the error.


View 1 Replies View Related

Select Multiple Tables Count

Oct 5, 2013

I'm trying to get the number of records from one table where a column matches another column in a 2nd table. I then need the total values of another column that it has selected.

SELECT HOLIDAY_REF].holiday_id, COUNT([BOOKING].booking_status_id) AS record_count COUNT([BOOKING].total_value) AS total_value FROM [HOLIDAY_REF] LEFT OUTER JOIN [BOOKING] ON [HOLIDAY_REF].holiday_id = [BOOKING].booking_status_id WHERE [BOOKING].holiday_id=[HOLIDAY_REF].holiday_id && booking_status_id = '330'

holiday_id | holiday_name
1 | Italy
2 | Russia
3 | Spain

holiday_id | booking_status_id | total_value
1 | 330 | 2500
3 | 330 | 1500
1 | 330 | 1750
2 | 330 | 1240
2 | 330 | 5600

Results would be:
Holiday_id | holiday_name | total_value | record_count
1 | Italy | 4250 | 2
2 | Russia | 6840 | 2
3 | Spain | 1500 | 1

Not sure I'm going about it the right way.

View 3 Replies View Related

Transact SQL :: Multiply Multiple Columns From One Row

Nov 23, 2015

I have a table with the following format

I am looking for a way to get the PRODUCT of all columns and group by M_DOMA, [FROM] - Basically multiply all columns on the row that are not M_DOMA or [FROM].

Is this possible using T-SQL 2012?

View 5 Replies View Related

Transact SQL :: Pivot With Multiple Columns

Sep 1, 2015

I have one table like this.

-- drop table #temp
create table #temp(ID bigint, Description varchar(50), ET varchar(200), ET_Status varchar(50), ET_Date datetime, ET_IsValid varchar(3))

insert into #temp
select * from (values (1,'Test','A', 'Ack','08/15/2015', 'Yes'),(1,'Test','B', 'Nack','08/17/2015', 'Yes'),(1,'Test','C', 'Ack','08/21/2015', 'Yes')) a(ID, Description, ET, ET_Status, ET_Date, ET_IsValid)

I want to pivot this. My expected result look like this.

ID - Description - ET_A_Status - ET_A_Date
- ET_A_IsValid -  ET_B_Status - ET_B_Date
  - ET_B_IsValid - ET_C_Status  - ET_C_Date

1  - Test    - 'Ack'       - '2015-08-15 00:00:00.000'  - 'Yes'   -  'Nack'  - '2015-08-17 00:00:00.000'  - 'Yes'  - 'Ack'   - '2015-08-21 00:00:00.000' -  'Yes'

View 6 Replies View Related

Transact SQL :: Top 10 Columns In All The Tables

Oct 21, 2015

In our production database we are looking top 10 columns in all the tables, for this using the below script, output was showing only one table how we get the all the tables top 10 ...

select top 10 * from ProductIdList

View 6 Replies View Related

COUNT Function And INNER JOIN On Multiple Tables

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

Count From Multiple Tables In A Single Report

Sep 11, 2014

how to count from multiple tables. So currently I pull two separate reports to show the count of TaskIDs by customer state and another to show WrapID by Customer state for last month:

TaskID Count by Customer state Query:
SELECT CU.CustomerState as 'State'
,Count (CM.TaskID) as 'CaseCount'
From Customer CU


I am wanting to add both these counts into a single report so i tried the follow query but the counts don't match to the reports I pull separately.

SELECT CU.CustomerState as 'State'
,Count (CM.TaskID) as 'CaseCount'
,Count (CW.WrapCodeID) as 'WrapCount'
From Customer CU
LEFT OUTER JOIN acn_ccpwrapcode


View 2 Replies View Related

Transact SQL :: First Values In Multiple Columns Selection

May 19, 2015

I'm trying to run something like this:

Select ID, FIRST(forename), FIRST(surname) from table1

I know First doesn't work in TSQL, I used to use it in Access and now need to run something like that in TSQL. Simply getting unique ID with first forename and surname, cause there are some dupes in a table.

There are records like:

ID      forename     surname
1    John    Kormack
1   James  Dope
2   Erin    Dupes
3   Will  Hugh
3    Walter Heisenberg

So I want to pull out:

1 John Kormack
2 Erin Dupes
3 Will Hugh

How can I run it in TSQL?

View 6 Replies View Related

Transact SQL :: How To Break Rows Into Multiple Columns

Jul 14, 2015

I have a table with 100 rows, 1 field (ID), and I would like to write a query to output it as 4 rows, and 25 columns.

Row data

Output will be like
c1  c2   c3    c4    c5....
1    5    9      13
2    6    10    14
3    7    11    15
4    8    12    16

View 4 Replies View Related

Transact SQL :: Multiple Columns Needs Median Calculation

Oct 27, 2015

I need to get the median for the 10 columns in my table. For the sake of example, I've reduced it to 2 columns. 

The code below works perfectly if i compute for only 1 column and certainly doesn't for multiple columns.

Is there a way to better handle the median computation in one pass, if multiple columns are involved?

DECLARE @tbl as table (id_n int, col1 int, col2 int)
insert into @tbl
values (1, 1, 2), (1,3, 4), (1, 5, 7), (2, 4, 7), (2, 7, 7), (2, 3, 5), (2,5, 5), (3, 1, 2), (3, 3, 5), (3, NULL,11)
select *
from @tbl
order by id_n, col1

[Code] .....

View 6 Replies View Related

Transact SQL :: Single Row Multiple Columns Into One Column

Jun 4, 2015

I have a requirements to make a single column using a date from multiple columns. below my DDL and sample.

Create table #Mainsample
(ItemNum nvarchar(35), ItemDate datetime, OPType int)
Insert into #Mainsample(ItemNum,ItemDate, OPType) values ('M9000000000020510095','2015-05-01 18:38:48.840',5)
Insert into #Mainsample(ItemNum,ItemDate, OPType) values ('M9000000000020510094','2015-05-02 20:38:40.850',5)
Insert into #Mainsample(ItemNum,ItemDate, OPType) values ('M9000000000020510092','2015-05-02 21:40:42.830',5)
Insert into #Mainsample(ItemNum,ItemDate, OPType) values ('353852061764483','2015-05-02 09:25:10.800',5)


View 10 Replies View Related

Transact SQL :: Row Number Over Partition By Multiple Columns

Sep 22, 2015

I have the following query

WITH summary AS
(SELECT tu.SequenceNumber,

[Code] ....

I am having trouble with the RowNumber Over Partition By portion of the query. I would like the query to return only the first occurrence of each sboinumber in the table for each trial id. It is only giving me the first occurrence of each sboinumber. I tried including the trialid in the partition by clause, but that is not working.

Sample Data
SequenceNumber   TrialID      SBOINumber
1                           1            5000
2                           1            5000
3                           2            5000
4                           2            5000
5                           1            5001
6                           3            5001
7                           3            5001

Should return SequenceNumber 1 and 3, 5, 6

View 11 Replies View Related

Transact SQL :: Merge Two Columns From Different Tables?

Oct 26, 2015

I have two temp. tables. I am trying to show the agents how makes the sales and the ones how didnt make sales based on the time that they clock in. One table is called #sales which has only the agents that make sales and other tables is #hours which has both agents that do not make sale. the problem is that I can not get both agents to show on my report. I tried different ways but I could not. #sales table uses (select statement from AmountStats table that stores only the agents who make sale). #hours table uses different tables to store all gents who makes sale and ones that are not making sale. 

@start datetime,
@end datetime,
@campaignId uniqueidentifier,
@campaignName varchar(250),
@segment varchar(50)

set @start = '2015-10-07'
set @end = '2015-10-20'


how to merge these two columns from #sales and #hours tables without duplication.

View 6 Replies View Related

Transact SQL :: Search Functionality With Multiple Columns - Writing SP

Jul 20, 2015

What is the most efficient way to write an SP to tackle all kinds of combinations here (where a user could give any search input).I know this must be fairly common to come across this situation.I have written an SP which will take in all the parameters and based on "IF" statements and using "LIKE" in SQL, this SP returns search results.But I wanted to know if there was more efficient ways of doing this, as you can imagine you might end up having several combinations of IF conditions.

View 9 Replies View Related

Transact SQL :: Sorting By Minimum Of Multiple DATETIME Columns

Apr 22, 2015

I have a view in my database detailing the expiry date of each credential for each employee. The view is designed as to display one record per employee and in that record is the expiry date of each credential and the days remaining. So the columns are as follows:-

Employee CodeExpiry Date (x8 columns) (named as credential e.g. [Passport])
Days Remaining (x8 columns) (named as "TS_" + Credential)

I'm trying to use the CASE function to compare each DATETIME column with one another and retrieve the minimum. How can I return the minimum date as a run-time column and sort the view by this column? My code is as follows:-

SELECT [Passport],[TS_Passport],[Visa],[TS_Visa],[Civil_ID],[TS_Civil_ID],[KOC_Pass],[TS_KOC_Pass],[JO_Pass],[TS_JO_Pass],
[Ratqa_Pass],[TS_Ratqa_Pass],[Driving_License],[TS_Driving_License],[Health_Book],[TS_Health_Book], CASE
WHEN Passport <= Visa AND Passport <= Civil_ID AND Passport <= KOC_Pass AND Passport <= JO_Pass AND


I've been told that this is the most efficient given the number of records in my database. The Min_Date is always NULL. I need the minimum of the 8 dates to be the Min_Date.

View 9 Replies View Related

Transact SQL :: Filtering Rows Based On Multiple Values In Columns?

Sep 22, 2015

In a table I have some rows with flag A & B for a scode, some scode with only A and some are only B flags.

I would like to fetch all rows with flag A when both flags are present, no rows with B should be fetched. Fetch all rows when only single flags are present for a scode.How to achieve this using TSQL code.

View 2 Replies View Related

Copyrights 2005-15, All rights reserved