Group By to form condensed reponse

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

SQL Server 2012 Forums

Transact-SQL (2012)

Group By to form condensed reponse

Author

Topic

philostheos
Starting Member

5 Posts

Posted - 2013-02-20 : 14:55:16

I have a view that represents customer invoices for a month. I need to form a query that will produce a single result set for each customer with columns containing the sums for 4 entries. The following sql will correctly sum the 4 entries, but each entry will have a separate row result.

SELECT
cust_id,
string_agg(inv_notes, ','),
cname,
cid,
cont_name,
caddr1,
caddr2,
caddr3,
caddr4,
cphone,
cemail,
description,
(select SUM(price) WHERE description = 'Consulting'),
(select SUM(price) WHERE description = 'Meals'),
(select SUM(price) WHERE description = 'Parking'),
(select SUM(price) WHERE description = 'Airfare')
FROM
public."InvoiceView"
GROUP BY
cust_id,
description,
cname,
cid,
cont_name,
caddr1,
caddr2,
caddr3,
caddr4,
cphone,
cemail;

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2013-02-20 : 15:02:55

SELECT
cust_id,
string_agg(inv_notes, ','),
cname,
cid,
cont_name,
caddr1,
caddr2,
caddr3,
caddr4,
cphone,
cemail,
SUM(CASE WHEN description = 'Consulting' THEN price) ELSE 0 END),
SUM(CASE WHEN description = 'Meals' THEN price ELSE 0 END),
SUM(CASE WHEN description = 'Parking' THEN price ELSE 0 END),
SUM(CASE WHEN description = 'Airfare' THEN price ELSE 0 END)
FROM
public."InvoiceView"
GROUP BY
cust_id,
cname,
cid,
cont_name,
caddr1,
caddr2,
caddr3,
caddr4,
cphone,
cemail;

Jim

Everyday I learn something that somebody else already knew

philostheos
Starting Member

5 Posts

Posted - 2013-02-20 : 15:14:34

Excellent response... perhaps I should consolidate that into the view, but that would create a group of inner and outer joins that I am not sure would work... but here goes. This response may take longer. I am using gnucash (Open Source) out of the box as it were using PostgreSQL. I have created the view InvoiceView as follows:

SELECT invoices.guid AS inv_guid, invoices.owner_guid AS cust_guid,
invoices.id AS inv_id, invoices.date_posted AS inv_date,
invoices.notes AS inv_notes, customers.name AS cname, customers.id AS cid,
customers.addr_name AS cont_name, customers.addr_addr1 AS caddr1,
customers.addr_addr2 AS caddr2, customers.addr_addr3 AS caddr3,
customers.addr_addr4 AS caddr4, customers.addr_phone AS cphone,
customers.addr_email AS cemail, entries.description, entries.i_price_num
FROM invoices, customers, entries
WHERE invoices.guid::text = entries.invoice::text AND invoices.owner_guid::text = customers.guid::text AND invoices.date_posted >= date_trunc('month'::text, now() - '1 mon'::interval) AND invoices.date_posted < date_trunc('month'::text, now())
ORDER BY customers.guid;

Now to add what was just done here:

SELECT
"InvoiceView".cust_guid,
string_agg("InvoiceView".inv_notes, '\n'),
"InvoiceView".cname,
"InvoiceView".cid,
"InvoiceView".cont_name,
"InvoiceView".caddr1,
"InvoiceView".caddr2,
"InvoiceView".caddr3,
"InvoiceView".caddr4,
"InvoiceView".cphone,
"InvoiceView".cemail,
SUM(CASE WHEN "InvoiceView".description = 'Consulting Time' THEN ("InvoiceView".i_price_num/10000) ELSE 0 END ),
SUM(CASE WHEN "InvoiceView".description = 'Airfare' THEN ("InvoiceView".i_price_num/10000) ELSE 0 END ),
SUM(CASE WHEN "InvoiceView".description = 'Meals' THEN ("InvoiceView".i_price_num/10000) ELSE 0 END ),
SUM(CASE WHEN "InvoiceView".description = 'Parking' THEN ("InvoiceView".i_price_num/10000) ELSE 0 END )
FROM
public."InvoiceView"
GROUP BY
"InvoiceView".cust_guid,
--"InvoiceView".description,
"InvoiceView".cname,
"InvoiceView".cid,
"InvoiceView".cont_name,
"InvoiceView".caddr1,
"InvoiceView".caddr2,
"InvoiceView".caddr3,
"InvoiceView".caddr4,
"InvoiceView".cphone,
"InvoiceView".cemail;

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2013-02-20 : 15:24:22

Views are good things, as you may need that code elsewhere. If price is an integer, multiply by 1.0,
i.e,SUM(CASE WHEN "InvoiceView".description = 'Parking' THEN ("InvoiceView".i_price_num*1.0/10000) ELSE 0 END )

Jim

Everyday I learn something that somebody else already knew

philostheos
Starting Member

5 Posts

Posted - 2013-02-20 : 22:25:45

Thanks Jim. However, I don't have the slightest clue how to do a group by query when multiple tables are involved. That is why I created the view first, then tried the group by query.

philostheos
Starting Member

5 Posts

Posted - 2013-02-20 : 23:01:45

Retract that statement, I used the first query in the from for the second query and created a view from that.

philostheos
Starting Member

5 Posts

Posted - 2013-02-21 : 13:02:28

One more question... Sometimes, but not always, the string_agg function is returning empty results (ie ",,,,") how do I check for empty (as opposed to null) results?

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2013-02-21 : 14:41:10

Well, this is actually a board for MS SQL Server, not PostGreSQL, although there are certainly similarities. In MS SQL (t-SQL) we check for null vs empty
using ISNULL. E.g., ISNULL(MyColumn,'') <> '' means MyColumn can't be null and can't be empty string, You may get better help at www.dbForums.com

Jim

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources