SQL select query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL select query

Author

Topic

seenuFour
Starting Member

16 Posts

Posted - 2009-06-04 : 09:15:54

Hello,

How to write a query that counts the total number of occurrences for each value in colA (TableA), in TableB.

Sample scenario
TableA
C1 C2
-----
1 X
2 Y

TableB
C1 C2 C3
------
1 X a
2 Y b
3 X c
4 X d
5 Y e

The output should show

C2 Count
--------
X 3
Y 2

Where Ci(i = 1 to 3), are columns.

Thanks

Srinivas

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-04 : 09:20:32

[code]
select a.C2, Count = count(*)
from tablea a
inner join tableb b on a.C2 = b.C2
group by a.C2
[/code]

KH
[spoiler]Time is always against us[/spoiler]

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-06-04 : 09:21:49

select c2,count(c2) from tableb group by c2

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

seenuFour
Starting Member

16 Posts

Posted - 2009-06-04 : 09:50:37

quote:
Originally posted by khtan

select a.C2, Count = count(*)
from tablea a
inner join tableb b on a.C2 = b.C2
group by a.C2

KH
[spoiler]Time is always against us[/spoiler]

Hi Khtan,

Thanks for your reply. I have to extend my query to 3 tables. Here is the sample scenario

Table1
C1 C2 C3 C4
------------
1 X A a
2 Y B b
3 Z C c

Table2
C1 C2 C5
----------
1 X x
2 Y y

Table3
C1 C2 C6
---------
1 X a
2 Y b
3 X c
4 X d
5 Y e
I need to write a query that prints the total number of occurrences for each value of column C2 in Table2, in Table3

The output should contain the following columns
T1.C2 T1.C3 T1.C4 T2.C5 count
-----------------------------
X A a x 3
Y B b y 2

Where Ci (i=1 to 5) are columns and T1 = Table1, T2 = Table2

Thanks a lot

Srinivas

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-04 : 10:01:30

[code]
select T1.C2, T1.C3, T1.C4, T2.C5, count = count(*)
from Table2 T2
inner join Table1 T1 on T2.C2 = T1.C2
inner join Table3 T3 on T2.C2 = T3.C2
group by T1.C2, T1.C3, T1.C4, T2.C5
[/code]

EDIT in red

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-04 : 10:02:06

is there a table 4 ?

KH
[spoiler]Time is always against us[/spoiler]

seenuFour
Starting Member

16 Posts

Posted - 2009-06-04 : 10:04:51

quote:

Originally posted by khtan


select T1.C2 T1.C3 T1.C4 T2.C5, count = count(*)
from   Table2 T2
       inner join Table1 T1 on T2.C2 = T1.C2
       inner join Table3 T3 on T2.C2 = T3.C2
group by T1.C2, T1.C3, T1.C4, T2.C5



EDIT in red

KH
[spoiler]Time is always against us[/spoiler]

Hi Khtan,

No, there is no Table4.

But are you sure this gives the required number of rows and the right value of count ????

T1.C2 T1.C3 T1.C4 T2.C5 count
-----------------------------
X A a x 3
Y B b y 2

If possible, can you explain your query.

Thanks

Srinivas

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-04 : 10:07:53

missed out the group by statement

KH
[spoiler]Time is always against us[/spoiler]

seenuFour
Starting Member

16 Posts

Posted - 2009-06-04 : 10:11:14

quote:
Originally posted by khtan

missed out the group by statement

KH
[spoiler]Time is always against us[/spoiler]

But are you sure this gives the required number of rows and the right value of count ????

T1.C2 T1.C3 T1.C4 T2.C5 count
-----------------------------
X A a x 3
Y B b y 2

If possible, can you explain your query.

Thanks

Srinivas

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-04 : 10:12:37

quote:
Originally posted by seenuFour

quote:
Originally posted by khtan

missed out the group by statement

KH
[spoiler]Time is always against us[/spoiler]

But are you sure this gives the required number of rows and the right value of count ????

T1.C2 T1.C3 T1.C4 T2.C5 count
-----------------------------
X A a x 3
Y B b y 2

If possible, can you explain your query.

Thanks

Srinivas

i don't know. why don't you try out and tell me ? I don't have your tables

KH
[spoiler]Time is always against us[/spoiler]

seenuFour
Starting Member

16 Posts

Posted - 2009-06-04 : 10:20:05

quote:
Originally posted by khtan

quote:
Originally posted by seenuFour

quote:
Originally posted by khtan

missed out the group by statement

KH
[spoiler]Time is always against us[/spoiler]

But are you sure this gives the required number of rows and the right value of count ????

T1.C2 T1.C3 T1.C4 T2.C5 count
-----------------------------
X A a x 3
Y B b y 2

If possible, can you explain your query.

Thanks

Srinivas

i don't know. why don't you try out and tell me ? I don't have your tables

KH
[spoiler]Time is always against us[/spoiler]

select T1.C2, T1.C3, T1.C4, T2.C5, count = count(*)
from Table2 T2 where T1.C2 = T2.C2 and
inner join Table3 T3 on T2.C2 = T3.C2
group by T1.C2, T1.C3, T1.C4, T2.C5

The count should only count all the occurrences in table T3.

Please let me know if this query serves the purpose.

I don't have the data in the tables yet. I am waiting on the DBA to populate the initial data.

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-04 : 11:31:20

quote:
Please let me know if this query serves the purpose.

I don't have the data in the tables yet. I am waiting on the DBA to populate the initial data.

I don't have the data & table at all but . . .


DECLARE @Table1 TABLE
(
	C1 	int,
	C2 	CHAR,
	C3 	CHAR,
	C4	CHAR
)
INSERT INTO @Table1
SELECT	1, 'X', 'A', 'a' UNION ALL
SELECT	2, 'Y', 'B', 'b' UNION ALL
SELECT	3, 'Z', 'C', 'c'

DECLARE @Table2 TABLE
(
	C1 	int,
	C2 	CHAR,
	C5	CHAR
)
INSERT INTO @Table2
SELECT	1, 'X', 'x' UNION ALL
SELECT	2, 'Y', 'y'

DECLARE @Table3 TABLE
(
	C1 	int,
	C2 	CHAR,
	C6	CHAR
)
INSERT INTO @Table3
SELECT	1, 'X', 'a' UNION ALL
SELECT	2, 'Y', 'b' UNION ALL
SELECT	3, 'X', 'c' UNION ALL
SELECT	4, 'X', 'd' UNION ALL
SELECT	5, 'Y', 'e'

SELECT T1.C2, T1.C3, T1.C4, T2.C5, COUNT = COUNT(*)
FROM   @Table2 T2
       INNER JOIN @Table1 T1 ON T2.C2 = T1.C2
       INNER JOIN @Table3 T3 ON T2.C2 = T3.C2
GROUP BY T1.C2, T1.C3, T1.C4, T2.C5

/*
C2   C3   C4   C5   COUNT       
---- ---- ---- ---- ----------- 
X    A    a    x    3
Y    B    b    y    2

(2 row(s) affected)
*/

KH
[spoiler]Time is always against us[/spoiler]

seenuFour
Starting Member

16 Posts

Posted - 2009-06-04 : 11:46:08

quote:

Originally posted by khtan

quote:
Please let me know if this query serves the purpose.

I don't have the data in the tables yet. I am waiting on the DBA to populate the initial data.

I don't have the data & table at all but . . .


DECLARE @Table1 TABLE
(
	C1 	int,
	C2 	CHAR,
	C3 	CHAR,
	C4	CHAR
)
INSERT INTO @Table1
SELECT	1, 'X', 'A', 'a' UNION ALL
SELECT	2, 'Y', 'B', 'b' UNION ALL
SELECT	3, 'Z', 'C', 'c'

DECLARE @Table2 TABLE
(
	C1 	int,
	C2 	CHAR,
	C5	CHAR
)
INSERT INTO @Table2
SELECT	1, 'X', 'x' UNION ALL
SELECT	2, 'Y', 'y'

DECLARE @Table3 TABLE
(
	C1 	int,
	C2 	CHAR,
	C6	CHAR
)
INSERT INTO @Table3
SELECT	1, 'X', 'a' UNION ALL
SELECT	2, 'Y', 'b' UNION ALL
SELECT	3, 'X', 'c' UNION ALL
SELECT	4, 'X', 'd' UNION ALL
SELECT	5, 'Y', 'e'

SELECT T1.C2, T1.C3, T1.C4, T2.C5, COUNT = COUNT(*)
FROM   @Table2 T2
       INNER JOIN @Table1 T1 ON T2.C2 = T1.C2
       INNER JOIN @Table3 T3 ON T2.C2 = T3.C2
GROUP BY T1.C2, T1.C3, T1.C4, T2.C5

/*
C2   C3   C4   C5   COUNT       
---- ---- ---- ---- ----------- 
X    A    a    x    3
Y    B    b    y    2

(2 row(s) affected)
*/

KH
[spoiler]Time is always against us[/spoiler]

Hi,

I still get an error when I execute the query:

SELECT T1.Competency, T1.CourseName, T2.StartDate, T1.Duration, COUNT = COUNT(*), T1.Capacity
FROM @CourseSchedule T2
INNER JOIN @Courses T1 ON T2.CourseNo = T1.CourseNo
INNER JOIN @Registrations T3 ON T2.CourseNo = T3.CourseNo
GROUP BY T1.Competency, T1.CourseName, T2.StartDate, T1.Duration, T1.Capacity

Here is the error:

Msg 1087, Level 15, State 2, Line 2
Must declare the table variable "@CourseSchedule".

What is that I am doing wrong.

Thanks

Srinivas

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-11 : 09:47:38

Do you have actual table ? or are you using table variable for the query ?

replace the table variable with your actual table name

KH
[spoiler]Time is always against us[/spoiler]

DavidDock
Starting Member

1 Post

Posted - 2009-06-15 : 03:06:28

This is the SQL select Query which are used to get all information in the database.
SELECT * FROM table_name

temporary jobs London and the UK

seenuFour
Starting Member

16 Posts

Posted - 2009-06-18 : 12:17:50

quote:

Originally posted by khtan

quote:
Please let me know if this query serves the purpose.

I don't have the data in the tables yet. I am waiting on the DBA to populate the initial data.

I don't have the data & table at all but . . .


DECLARE @Table1 TABLE
(
	C1 	int,
	C2 	CHAR,
	C3 	CHAR,
	C4	CHAR
)
INSERT INTO @Table1
SELECT	1, 'X', 'A', 'a' UNION ALL
SELECT	2, 'Y', 'B', 'b' UNION ALL
SELECT	3, 'Z', 'C', 'c'

DECLARE @Table2 TABLE
(
	C1 	int,
	C2 	CHAR,
	C5	CHAR
)
INSERT INTO @Table2
SELECT	1, 'X', 'x' UNION ALL
SELECT	2, 'Y', 'y'

DECLARE @Table3 TABLE
(
	C1 	int,
	C2 	CHAR,
	C6	CHAR
)
INSERT INTO @Table3
SELECT	1, 'X', 'a' UNION ALL
SELECT	2, 'Y', 'b' UNION ALL
SELECT	3, 'X', 'c' UNION ALL
SELECT	4, 'X', 'd' UNION ALL
SELECT	5, 'Y', 'e'

SELECT T1.C2, T1.C3, T1.C4, T2.C5, COUNT = COUNT(*)
FROM   @Table2 T2
       INNER JOIN @Table1 T1 ON T2.C2 = T1.C2
       INNER JOIN @Table3 T3 ON T2.C2 = T3.C2
GROUP BY T1.C2, T1.C3, T1.C4, T2.C5

/*
C2   C3   C4   C5   COUNT       
---- ---- ---- ---- ----------- 
X    A    a    x    3
Y    B    b    y    2

(2 row(s) affected)
*/

KH
[spoiler]Time is always against us[/spoiler]

Hi Khtan,

I am having trouble with this query, If the table T2 has following entries

C1 C2 C5
-----------
1, X, x
2, Y, y
3, Z, z

Then the out put should show
C2 C3 C4 C5 COUNT
---- ---- ---- ---- -----------
X A a x 3
Y B b y 2
Z C c z 0

I think, I have to do , outer Join ???

I tried this:
SELECT T1.C2, T1.C3, T1.C4, T2.C5, COUNT = COUNT(*)
FROM @Table2 T2
INNER JOIN @Table1 T1 ON T2.C2 = T1.C2
LEFT JOIN @Table3 T3 ON T2.C2 = T3.C2
GROUP BY T1.C2, T1.C3, T1.C4, T2.C5

Gives me an WRONG output:

C2 C3 C4 C5 COUNT
---- ---- ---- ---- -----------
X A a x 3
Y B b y 2
Z C c z 1

The count should be 0, it prints 1

How should I write the query using Left Join and print correct value for count.

Thanks

Srinivas

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-18 : 12:19:52

which outer join do you want ? LEFT OUTER JOIN ? RIGHT OUTER JOIN ? FULL OUTER JOIN ?

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-18 : 12:28:33

quote:
How should I write the query using Left Join and print correct value for count.

You can make things easier for those who try to help you by providing the table structure in declare and sample data in insert into statements similar to what i have done in my post.

KH
[spoiler]Time is always against us[/spoiler]

seenuFour
Starting Member

16 Posts

Posted - 2009-06-18 : 12:31:35

quote:
Originally posted by khtan

which outer join do you want ? LEFT OUTER JOIN ? RIGHT OUTER JOIN ? FULL OUTER JOIN ?

KH
[spoiler]Time is always against us[/spoiler]

LEFT OUTER JOIN , I think, I'll give the scenario for your reference:

Table1
C1 C2 C3 C4
------------
1 X A a
2 Y B b
3 Z C c

Table2
C1 C2 C5
----------
1 X x
2 Y y
3 Z z

Table3
C1 C2 C6
---------
1 X a
2 Y b
3 X c
4 X d
5 Y e

I should get output like this:

C2 C3 C4 C5 COUNT
---- ---- ---- ---- -----------
X A a x 3
Y B b y 2
Z C c z 0

I tried left, right and full joins

SELECT T1.C2, T1.C3, T1.C4, T2.C5, COUNT = COUNT(*)
FROM @Table2 T2
INNER JOIN @Table1 T1 ON T2.C2 = T1.C2
LEFT JOIN @Table3 T3 ON T2.C2 = T3.C2
GROUP BY T1.C2, T1.C3, T1.C4, T2.C5

LEFT and FULL JOINs print

C2 C3 C4 C5 COUNT
---- ---- ---- ---- -----------
X A a x 3
Y B b y 2
Z C c z 1

RIGHT JOIN prints
C2 C3 C4 C5 COUNT
---- ---- ---- ---- -----------
X A a x 3
Y B b y 2

Please tell me the correct way to use Join

Thanks

Srinivas

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-06-18 : 15:42:33

declare @x table (c1 int, c2 char(1),c3 char(1),c4 char(1))
insert @x
select 1,'X','A','a' union all
select 2,'Y','B','b' union all
select 3,'Z','C','c'

declare @y table (c1 int, c2 char(1),c5 char(1))
insert @y
select 1,'X','x' union all
select 2,'Y','y' union all
select 3,'Z','z'

declare @z table (c1 int, c2 char(1), c6 char(1))
insert @z
select 1, 'X' ,'a' union all
select 2, 'Y', 'b' union all
select 3, 'X', 'c' union all
select 4, 'X', 'd' union all
select 5, 'Y', 'e'

select x.c2,x.c3,x.c4,y.c5, coalesce(z.[count] ,0)
from @x x inner join @y y on x.c2 = y.c2
left join (select count(*) as [count],c2 from @z group by c2) z
on x.c2=z.c2

seenuFour
Starting Member

16 Posts

Posted - 2009-06-18 : 16:07:55

quote:
Originally posted by vijayisonly

declare @x table (c1 int, c2 char(1),c3 char(1),c4 char(1))
insert @x
select 1,'X','A','a' union all
select 2,'Y','B','b' union all
select 3,'Z','C','c'

declare @y table (c1 int, c2 char(1),c5 char(1))
insert @y
select 1,'X','x' union all
select 2,'Y','y' union all
select 3,'Z','z'

declare @z table (c1 int, c2 char(1), c6 char(1))
insert @z
select 1, 'X' ,'a' union all
select 2, 'Y', 'b' union all
select 3, 'X', 'c' union all
select 4, 'X', 'd' union all
select 5, 'Y', 'e'

select x.c2,x.c3,x.c4,y.c5, coalesce(z.[count] ,0)
from @x x inner join @y y on x.c2 = y.c2
left join (select count(*) as [count],c2 from @z group by c2) z
on x.c2=z.c2

Hi Vijay,

Thanks for your reply. Can you make sure that your query works fine ?

I am finding it hard to understand.

Lets say the table names here are Courses, and it has values:

C1 C2 C3 C4
------------
1 X A a
2 Y B b
3 Z C c

Second table name is CourseSchedule and it has values:

C1 C2 C5
----------
1 X x
2 Y y
3 Z z

Third table name is Registrations and it has values:

C1 C2 C6
---------
1 X a
2 Y b
3 X c
4 X d
5 Y e

The requirement is I have to count total number of occurrences for each value of column C2 in CourseSchedule, in Registrations.

The output I need is

C2 C3 C4 C5 COUNT
---- ---- ---- ---- -----------
X A a x 3
Y B b y 2
Z C c z 0

Earlier I tried this query:

SELECT Courses.C2, Courses.C3, Courses.C4, CourseSchedule.C5, COUNT = COUNT(*)
FROM CourseSchedule
INNER JOIN Courses ON CourseSchedule.C2 = Courses.C2
LEFT JOIN Registrations ON CourseSchedule.C2 = Registrations .C2
GROUP BY Courses.C2, Courses.C3, Courses.C4, CourseSchedule.C5

This prints wrong value for count in the third row as 1, instead fo 0:
C2 C3 C4 C5 COUNT
---- ---- ---- ---- -----------
X A a x 3
Y B b y 2
Z C c z 1

Can you please use above tables and tell me if your query works and gives the above output ?

Regards
Srinivas

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources