SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Group By to form condensed reponse
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

philostheos
Starting Member

USA
5 Posts

Posted - 02/20/2013 :  14:55:16  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
2870 Posts

Posted - 02/20/2013 :  15:02:55  Show Profile  Reply with Quote
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
Go to Top of Page

philostheos
Starting Member

USA
5 Posts

Posted - 02/20/2013 :  15:14:34  Show Profile  Reply with Quote
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;
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2870 Posts

Posted - 02/20/2013 :  15:24:22  Show Profile  Reply with Quote
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
Go to Top of Page

philostheos
Starting Member

USA
5 Posts

Posted - 02/20/2013 :  22:25:45  Show Profile  Reply with Quote
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.
Go to Top of Page

philostheos
Starting Member

USA
5 Posts

Posted - 02/20/2013 :  23:01:45  Show Profile  Reply with Quote
Retract that statement, I used the first query in the from for the second query and created a view from that.
Go to Top of Page

philostheos
Starting Member

USA
5 Posts

Posted - 02/21/2013 :  13:02:28  Show Profile  Reply with Quote
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?
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2870 Posts

Posted - 02/21/2013 :  14:41:10  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000