Hi
I am trying to work out if this is possible in a select query
I have a group by query which could result in several rows, what I want
to do is do a text equivalent of a sum() eg:
SELECT sum(inv_id),dat e,cust
from invoice
group by date,cust
Is there any way to get to a single concatenated inv_id field with just
one row?
thanks
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster 2 2028
mike wrote: Hi
I am trying to work out if this is possible in a select query
I have a group by query which could result in several rows, what I want to do is do a text equivalent of a sum() eg:
SELECT sum(inv_id),dat e,cust from invoice group by date,cust
Is there any way to get to a single concatenated inv_id field with just one row?
Yes, you can define your own aggregate. For example, I did this the
other day:
/*
Custom aggregate
This aggregate is so we can aggregate text into paragraph blocks
*/
CREATE OR REPLACE FUNCTION join_paras(text , text) RETURNS text AS
'
SELECT CASE
WHEN ($1 = '''') THEN $2
ELSE $1 || ''\n'' || $2
END;
' LANGUAGE 'SQL' IMMUTABLE;
CREATE AGGREGATE agg_paras (sfunc1=join_pa ras, basetype=text,
stype1=text, initcond1='');
Note I defined my own text-concatenation function because I wanted to
insert newlines between each block of text. If you just wanted joined
text you could use the built-in textcat()
Full specs of create aggregate are in the manuals.
HTH
--
Richard Huxton
Archonet Ltd
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postg resql.org
On Wed, 2004-09-08 at 17:13 +0100, Richard Huxton wrote: mike wrote: Hi
I am trying to work out if this is possible in a select query
I have a group by query which could result in several rows, what I want to do is do a text equivalent of a sum() eg:
SELECT sum(inv_id),dat e,cust from invoice group by date,cust
Is there any way to get to a single concatenated inv_id field with just one row?
Yes, you can define your own aggregate. For example, I did this the other day:
/* Custom aggregate This aggregate is so we can aggregate text into paragraph blocks */ CREATE OR REPLACE FUNCTION join_paras(text , text) RETURNS text AS ' SELECT CASE WHEN ($1 = '''') THEN $2 ELSE $1 || ''\n'' || $2 END; ' LANGUAGE 'SQL' IMMUTABLE;
CREATE AGGREGATE agg_paras (sfunc1=join_pa ras, basetype=text, stype1=text, initcond1='');
Note I defined my own text-concatenation function because I wanted to insert newlines between each block of text. If you just wanted joined text you could use the built-in textcat()
Full specs of create aggregate are in the manuals.
HTH
thanks - as sods law dictates I found a solution just after I posted
along the same lines, after a couple of hours of fruitless googling.
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Eric J Owens |
last post by:
TIA for any help!
I have an a2k mdb front end using sql server 2k on backend. my form show
router port assignments, when an assignment is deleted, there is a button
that is supposed to set the fields to null. All my text fields, though, end
up being updated to a '0' or '-1' instead of a null value. The sql server is
set to allow nulls in these fields.
here is my code: (each docmd statement is on a single line)
|
by: Todd D. Levy |
last post by:
I have a query based report where a number of the fields in each record
are Yes/No data fields.
I would like this report to behave as follows:
If ALL of the Yes/No data fields in a given record have a value of YES,
I would like that particular record to be suppressed (I.e. NOT appear in
the report).
and
|
by: Joe Stanton |
last post by:
Hello Group
I have a query that works in Oracle and SQL Server, but fails in Microsoft
Access.
The query is:
SELECT data fromTABLE1 WHERE data>='A&' AND data<'A'''
Here is my sample data:
|
by: Bob |
last post by:
I've got a bound report with a query as the record source. I'm adding
a total to the footer and have inserted a text box to display it. I'm
attempting to set the control source of the text box to a different sum
aggregate query using the expression builder. When I select the query
and it's single field, single row field it inserts the following in the
control source box for the text box.
=!
I've tested the query (...
|
by: serge |
last post by:
How can I run a single SP by asking multiple sales question either
by using the logical operator AND for all the questions; or using
the logical operator OR for all the questions. So it's always
either AND or OR but never mixed together.
We can use Northwind database for my question, it is very similar
to the structure of the problem on the database I am working on.
IF(SELECT OBJECT_ID('REPORT')) IS NOT NULL
DROP TABLE REPORT_SELECTION
| |
by: george.lengel |
last post by:
Hello experts,
I have been struggling for days to solve this problem and every
suggestion I find via Google does not work for me. There is probably a
solution out there that will do what I want, but I probably have not
properly implemented the solutions I find.
I am trying to make a page to allow personnel the ability to search our
backend DB (which is Ingres 2.0) through a web interface. Ingres has a
cgi program called ICE that can...
|
by: clare at snyder.on.ca |
last post by:
I have a SQL query I need to design to select name and email addresses
for policies that are due and not renewed in a given time period. The
problem is, the database keeps the information for every renewal in
the history of the policyholder.
The information is in 2 tables, policy and customer, which share the
custid data. The polno changes with every renewal Renewals in 2004
would be D, 2005 S, and 2006 L. polexpdates for a given customer...
|
by: jwgoerlich |
last post by:
Hello group,
I am working on a query string class. The purpose is to parse
name-value pairs from incoming text. Currently, I am using the Regex
code below. I have two questions.
First, the code below does not work if there is a space in the name.
For example, the text "Initial Catalog=test;" parses to name=Catalog
and value=test.
|
by: Darsin |
last post by:
Hi all
I need to perform a summation on a column of a table based on a
criteria given in another column in the same table. The catch is i
need to perform different sums according to the number of criterias in
the criteria column, in a single query. the structure of the table is
somethinmg like this (only relevant columns are shown)
TABLE1
Value - numeric(20,6)
Month - int
|
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: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
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: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
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: 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: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
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...
| |