Hi,
I have the following SQL
SELECT Table1.Col1, Table3.Col1 AS Expr1,
COUNT(Table1.Co l2) AS Col2_No, COUNT(Table1.Co l3) AS Col3_No etc,
FROM Table3
INNER JOIN Table2 ON Table3.Col1=Tab le2.Col1
RIGHT OUTER JOIN Table1 ON Table2.Col2=Tab le2.Col2
GROUP BY Table1.Col1, Table3.Col1
The output rows have a value in either Table1.Col1 or Table3.Col1 but not
both.
I'd like to combine Table1.Col1 and Table3.Col1 and group by the combined
column in the result but don't know how.
Thanks gratefully 5 6053
Hi
It would help if you posted the DDL (Create Table Statements) , example data
(as insert statements) and expected output. From your description it is not
100% clear how the tables relate or what results you expect.
If the values of Col1 are unique between each table your solution might be:
SELECT Col1, COUNT(Col2) as Col2No, COUNT(Col3) as Col3No
FROM Table1
GROUP BY Col1
UNION
SELECT Col1, COUNT(Col2) as Col2No, COUNT(Col3) as Col3No
FROM Table3
GROUP BY Col1
If not
SELECT IsNULL(T1.Col1, T3.Col1), COUNT(CASE WHEN T1.Col1 IS NULL THEN T1.Col2
ELSE T3.Col2 END ) AS Col2No, COUNT(CASE WHEN T1.Col1 IS NULL THEN T1.Col3
ELSE T3.Col3 END ) AS Col3No
FROM Table1 T1
LEFT JOIN Table3 T3 ON T1.Col2 = T3.Col2
GROUP BY IsNULL(T1.Col1, T3.Col1)
or more probably
SELECT Col1, SUM(Col2No) as Col2No, SUM(Col3No) as Col3No
FROM (
SELECT Col1, COUNT(Col2) as Col2No, COUNT(Col3) as Col3No
FROM Table1
GROUP BY Col1
UNION
SELECT Col1, COUNT(Col2) as Col2No, COUNT(Col3) as Col3No
FROM Table3
GROUP BY Col1 ) A
GROUP BY Col1
John
"JackT" <tu***********@ ntlworld.com> wrote in message
news:ov******** **********@news fep2-win.server.ntli .net... Hi, I have the following SQL
SELECT Table1.Col1, Table3.Col1 AS Expr1, COUNT(Table1.Co l2) AS Col2_No, COUNT(Table1.Co l3) AS Col3_No etc, FROM Table3 INNER JOIN Table2 ON Table3.Col1=Tab le2.Col1 RIGHT OUTER JOIN Table1 ON Table2.Col2=Tab le2.Col2 GROUP BY Table1.Col1, Table3.Col1
The output rows have a value in either Table1.Col1 or Table3.Col1 but not both. I'd like to combine Table1.Col1 and Table3.Col1 and group by the combined column in the result but don't know how. Thanks gratefully
Thanks John,
I didn't explain too well so I'll detail tables, releationships and what I'm
trying to do. I have managed to reduce & simplify the issue to two tables:-
Targets table which has columns:
target id - key identity autoincrement integer
locationid - integer
Actions table which has columns:
actionid - key identity autoincrement integer
targetid - integer
locationid integer
relationship is Targets RIGHT OUTER JOIN Actions ON Targets.targeti d =
Actions.targeti d (I want results from all rows in Actions).
I want to count all rows from Actions and group by locationid combined from
both tables.
Targets content:
targetid locationid
1 1
2 1
Actions Content:
actionid targetid locationid
1 NULL 1
2 NULL 2
3 NULL 3
4 1 NULL
5 1 NULL
6 2 NULL
If I use:
SELECT Actions.locatio nid, Targets.locatio nid, COUNT(actionid) AS actions
FROM Targets RIGHT JOIN Actions ON Targets.targeti d = Actions.target id
GROUP BY Actions.locatio nid, Targets.locatio nid
I get:
Actions Actions.locatio nid Targets.locatio nid
1 1 NULL
1 2 NULL
1 3 NULL
3 NULL 1
I want to combine both locationid columns in result giving:
Actions locationid
4 1
1 2
1 3
There are more columns than illustrated but if you the above can be cracked,
I'll be away!
Cheers,
Jack
"John Bell" <jb************ @hotmail.com> wrote in message
news:3f******** *************** @news.easynet.c o.uk... Hi
It would help if you posted the DDL (Create Table Statements) , example
data (as insert statements) and expected output. From your description it is
not 100% clear how the tables relate or what results you expect.
If the values of Col1 are unique between each table your solution might
be: SELECT Col1, COUNT(Col2) as Col2No, COUNT(Col3) as Col3No FROM Table1 GROUP BY Col1 UNION SELECT Col1, COUNT(Col2) as Col2No, COUNT(Col3) as Col3No FROM Table3 GROUP BY Col1
If not
SELECT IsNULL(T1.Col1, T3.Col1), COUNT(CASE WHEN T1.Col1 IS NULL THEN
T1.Col2 ELSE T3.Col2 END ) AS Col2No, COUNT(CASE WHEN T1.Col1 IS NULL THEN T1.Col3 ELSE T3.Col3 END ) AS Col3No FROM Table1 T1 LEFT JOIN Table3 T3 ON T1.Col2 = T3.Col2 GROUP BY IsNULL(T1.Col1, T3.Col1)
or more probably
SELECT Col1, SUM(Col2No) as Col2No, SUM(Col3No) as Col3No FROM ( SELECT Col1, COUNT(Col2) as Col2No, COUNT(Col3) as Col3No FROM Table1 GROUP BY Col1 UNION SELECT Col1, COUNT(Col2) as Col2No, COUNT(Col3) as Col3No FROM Table3 GROUP BY Col1 ) A GROUP BY Col1
John
John,
Thanks for putting me on the right track. With ref to the example in my
reply post I used:
SELECT ISNULL(Actions. locationid, Targets.locatio nid) AS Location,
COUNT(Actions.a ctionid) AS Actions_No
FROM Actions LEFT OUTER JOIN
Targets ON Actions.targeti d = Targets.targeti d
GROUP BY ISNULL(Actions. locationid, Targets.locatio nid)
All the other columns I want to count are in the Actions table so I just
need to add them to the SELECT statement.
Thanks again,
Jack
"JackT" <tu***********@ ntlworld.com> wrote in message
news:Fu******** ***********@new sfep2-win.server.ntli .net... Thanks John, I didn't explain too well so I'll detail tables, releationships and what
I'm trying to do. I have managed to reduce & simplify the issue to two
tables:- Targets table which has columns: target id - key identity autoincrement integer locationid - integer
Actions table which has columns: actionid - key identity autoincrement integer targetid - integer locationid integer
relationship is Targets RIGHT OUTER JOIN Actions ON Targets.targeti d = Actions.targeti d (I want results from all rows in Actions).
I want to count all rows from Actions and group by locationid combined
from both tables.
Targets content: targetid locationid 1 1 2 1
Actions Content: actionid targetid locationid 1 NULL 1 2 NULL 2 3 NULL 3 4 1 NULL 5 1 NULL 6 2 NULL
If I use: SELECT Actions.locatio nid, Targets.locatio nid, COUNT(actionid) AS actions FROM Targets RIGHT JOIN Actions ON Targets.targeti d = Actions.target id GROUP BY Actions.locatio nid, Targets.locatio nid
I get: Actions Actions.locatio nid Targets.locatio nid 1 1 NULL 1 2 NULL 1 3 NULL 3 NULL 1
I want to combine both locationid columns in result giving: Actions locationid 4 1 1 2 1 3
There are more columns than illustrated but if you the above can be
cracked, I'll be away! Cheers, Jack
Hi
It sounds like it worked then!
Here is usable DDL and example data in case you need it again.
create table Targets (
targetid integer NOT NULL identity (1,1) CONSTRAINT PK_Targets PRIMARY KEY,
locationid integer,
)
create table Actions (
actionid integer NOT NULL identity (1,1) CONSTRAINT PK_Actions PRIMARY KEY,
targetid integer NULL,
locationid integer,
CONSTRAINT FK_Actions FOREIGN KEY (TargetId) REFERENCES Targets(TargetI d)
)
INSERT INTO Targets (locationid) VALUES (1)
INSERT INTO Targets (locationid) VALUES (1)
INSERT INTO Actions (targetid, locationid) VALUES (NULL,1)
INSERT INTO Actions (targetid, locationid) VALUES (NULL,2)
INSERT INTO Actions (targetid, locationid) VALUES (NULL,3)
INSERT INTO Actions (targetid, locationid) VALUES (1,NULL)
INSERT INTO Actions (targetid, locationid) VALUES (1,NULL)
INSERT INTO Actions (targetid, locationid) VALUES (2,NULL)
SELECT * FROM Targets
/*
targetid locationid
----------- -----------
1 1
2 1
(2 row(s) affected)
*/
SELECT * FROM Actions
/*
actionid targetid locationid
----------- ----------- -----------
1 NULL 1
2 NULL 2
3 NULL 3
4 1 NULL
5 1 NULL
6 2 NULL
(6 row(s) affected)
*/
-- Your attempt
SELECT A.locationid, T.locationid, COUNT(A.actioni d) AS actions
FROM Targets T RIGHT JOIN Actions A ON T.targetid = A.targetid
GROUP BY A.locationid, T.locationid
/*
locationid locationid actions
----------- ----------- -----------
1 NULL 1
2 NULL 1
3 NULL 1
NULL 1 3
(4 row(s) affected)
*/
-- Your second attempt
SELECT ISNULL(A.locati onid, T.locationid) AS Location,
COUNT(A.actioni d) AS Actions_No
FROM Actions A LEFT OUTER JOIN Targets T ON A.targetid = T.targetid
GROUP BY ISNULL(A.locati onid, T.locationid)
/* Gives
Location Actions_No
----------- -----------
1 4
2 1
3 1
(3 row(s) affected)
*/
John
"JackT" <tu***********@ ntlworld.com> wrote in message
news:49******** ***********@new sfep2-win.server.ntli .net... John,
Thanks for putting me on the right track. With ref to the example in my reply post I used:
SELECT ISNULL(Actions. locationid, Targets.locatio nid) AS Location, COUNT(Actions.a ctionid) AS Actions_No FROM Actions LEFT OUTER JOIN Targets ON Actions.targeti d = Targets.targeti d GROUP BY ISNULL(Actions. locationid, Targets.locatio nid)
All the other columns I want to count are in the Actions table so I just need to add them to the SELECT statement. Thanks again, Jack
"JackT" <tu***********@ ntlworld.com> wrote in message news:Fu******** ***********@new sfep2-win.server.ntli .net... Thanks John, I didn't explain too well so I'll detail tables, releationships and what I'm trying to do. I have managed to reduce & simplify the issue to two tables:- Targets table which has columns: target id - key identity autoincrement integer locationid - integer
Actions table which has columns: actionid - key identity autoincrement integer targetid - integer locationid integer
relationship is Targets RIGHT OUTER JOIN Actions ON Targets.targeti d = Actions.targeti d (I want results from all rows in Actions).
I want to count all rows from Actions and group by locationid combined
from both tables.
Targets content: targetid locationid 1 1 2 1
Actions Content: actionid targetid locationid 1 NULL 1 2 NULL 2 3 NULL 3 4 1 NULL 5 1 NULL 6 2 NULL
If I use: SELECT Actions.locatio nid, Targets.locatio nid, COUNT(actionid) AS
actions FROM Targets RIGHT JOIN Actions ON Targets.targeti d = Actions.target id GROUP BY Actions.locatio nid, Targets.locatio nid
I get: Actions Actions.locatio nid Targets.locatio nid 1 1 NULL 1 2 NULL 1 3 NULL 3 NULL 1
I want to combine both locationid columns in result giving: Actions locationid 4 1 1 2 1 3
There are more columns than illustrated but if you the above can be cracked, I'll be away! Cheers, Jack
Thanks John,
Appreciate your informative close-out post and will certainly file for
reference.
Cheers,
Jack
"John Bell" <jb************ @hotmail.com> wrote in message
news:3f******** *************** @news.easynet.c o.uk... Hi
It sounds like it worked then!
Here is usable DDL and example data in case you need it again.
create table Targets ( targetid integer NOT NULL identity (1,1) CONSTRAINT PK_Targets PRIMARY
KEY, locationid integer, )
create table Actions ( actionid integer NOT NULL identity (1,1) CONSTRAINT PK_Actions PRIMARY
KEY, targetid integer NULL, locationid integer, CONSTRAINT FK_Actions FOREIGN KEY (TargetId) REFERENCES Targets(TargetI d) )
INSERT INTO Targets (locationid) VALUES (1) INSERT INTO Targets (locationid) VALUES (1)
INSERT INTO Actions (targetid, locationid) VALUES (NULL,1) INSERT INTO Actions (targetid, locationid) VALUES (NULL,2) INSERT INTO Actions (targetid, locationid) VALUES (NULL,3) INSERT INTO Actions (targetid, locationid) VALUES (1,NULL) INSERT INTO Actions (targetid, locationid) VALUES (1,NULL) INSERT INTO Actions (targetid, locationid) VALUES (2,NULL)
SELECT * FROM Targets
/* targetid locationid ----------- ----------- 1 1 2 1
(2 row(s) affected) */ SELECT * FROM Actions
/* actionid targetid locationid ----------- ----------- ----------- 1 NULL 1 2 NULL 2 3 NULL 3 4 1 NULL 5 1 NULL 6 2 NULL
(6 row(s) affected) */
-- Your attempt SELECT A.locationid, T.locationid, COUNT(A.actioni d) AS actions FROM Targets T RIGHT JOIN Actions A ON T.targetid = A.targetid GROUP BY A.locationid, T.locationid
/* locationid locationid actions ----------- ----------- ----------- 1 NULL 1 2 NULL 1 3 NULL 1 NULL 1 3
(4 row(s) affected) */
-- Your second attempt SELECT ISNULL(A.locati onid, T.locationid) AS Location, COUNT(A.actioni d) AS Actions_No FROM Actions A LEFT OUTER JOIN Targets T ON A.targetid = T.targetid GROUP BY ISNULL(A.locati onid, T.locationid)
/* Gives Location Actions_No ----------- ----------- 1 4 2 1 3 1
(3 row(s) affected) */
John This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Ilan |
last post by:
Hi all
I need to add data from two Excel sheets (both on the same workbook)
to an existing table in my SQL DB.
The problem is that each sheet holds different fields for the same
record, though the records are sorted by row numbers.
(I had to split the fields to different sheets because Excel has a
limit of 256 fields in each sheet)
My sheets are quite large (~55,000 rows and 200 columns each) and I'll
have to repeat this action many...
|
by: Alan Little |
last post by:
Viewed in IE, this page is exactly what I'm trying to do:
http://www.holotech.net/links/
Header, two fixed-width columns, and a footer. However, in NS and Opera,
the second column slides to the left, on top of the first column. I've read
all the tutorials and examples I could find, but everything else ends up
with the second column all the way over on the right side of the window. I
just want it butted up against the first column. I...
|
by: M.Stanley |
last post by:
Hi,
I'm attempting to create a query that will combine 2 columns of
numbers into one. The followng comes from 1 table with 4 fields
(A,B,C,D)
A B RESULT
700 000 700000
700 001 700001
700 002 700002
..
|
by: strauss.sean |
last post by:
To all:
Here's an interesting one: I have a query that produces 56 (!!) columns
of data. The first three columns are "to repeat at left", as can be
selected in Excel at File --> Page Setup --> Sheet tab. That is, the
first page prints (say) 10 columns. The second page prints the first
three columns, and the next seven columns, and so on. Each page would
do this until all columns are printed.
Access gives a limit to the amount of...
|
by: Don |
last post by:
I'm thinking this is simple and I'm just not understanding how to do it but
I have to ask because I'm stumped.
I have a query (which is used to create a report) and three of the columns
in this query are Date, Charge by Hour, and Charge by Day. In my report all
three of these columns show. I'd like to have only two columns in my
report; Date, Charge (charge would consist of Charge by Hour and Charge by
Day). How can I make that...
| |
by: mkopcic |
last post by:
Hi all!
I need help with Visual Basic Application 6.0 and Excel.
Problem is that I need group some columns in Excel by Macro in VBA.
Columns are not fixed which mean that every time when a program starts columns
get changed. So that columns I calculate and put them in string.
How can I group those columns in Excel document? I was trying something like
Range (String).group but that doesn't work.
|
by: J055 |
last post by:
Hi
I need to search a number of DataTables within a DataSet (with some
relationships) and then display the filtered results in a GridView. The
Columns that need to be displayed come from 2 of the tables but the search
needs check 3 or 4 tables. Do I need to create a new DataTable which has the
columns from both the tables so I can display in the Gridview or can I get
the columns somehow from the DataSet without creating a new table?
I...
|
by: Ken Fine |
last post by:
This is a question that someone familiar with ASP.NET and ADO.NET DataSets
and DataTables should be able to answer fairly easily. The basic question is
how I can efficiently match data from one dataset to data in a second
dataset, using a common key. I will first describe the problem in words and
then I will show my code, which has most of the solution done already.
I have built an ASP.NET that queries an Index Server and returns a...
|
by: MrArjP |
last post by:
So far, I have been researching and Microsoft.Office.Excel namespace is used alot, unfortunately with my work I have been ordered to use infragistics and so far I have had no luck with finding any function that can group columns together, does anyone know of any function or specific way of grouping columns? Just in case anyone misunderstands I mean grouping in the sense that you have a button or something visible there for you to have the choice...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
| |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |