Beginner Programming
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
User Name:
Password:
Remember me
Go Back   Dev Shed Forums > Other > Beginner Programming
Reload this Page

Unable to get GROUP BY to work

Discuss Unable to get GROUP BY to work in the Beginner Programming forum on Dev Shed.
Unable to get GROUP BY to work Beginner Programming forum discussing problems and solutions for just about any issue. Experienced programmers offer their help to those just starting out.

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
« Previous Thread | Next Thread »  
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread Dev Shed Forums Sponsor:
Get inside! Sample the range of functionality easily built with JMSL Library for Time Series Data Analysis, Heat Maps, Portfolio Optimization, Monte Carlo Simulation, Stock Price Charting and more. Download Now!
  #1  
Old July 1st, 2003, 12:16 PM
ecwaddel99 ecwaddel99 is offline
Junior Member
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Jun 2003
Posts: 7 ecwaddel99 User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
Question Unable to get GROUP BY to work
I have been working on this for a day or so. The ORDER BY works fine. but when I insert the GROUP BY the script no longer works. In fact it returns no data.

I have used PERL's debugger and this is what I get; "Attribute vqtdb1.dte_submit must be GROUPed or used in an aggregate function", but this has been of little help.

What do I need to do to get the GROUP BY function to work?

###########################

$dbh1 = DBI->connect('DBI:Pg:dbname=bandp')
or die "Couldn't connect to database: " . DBI->errstr;
$sth1 = $dbh1->prepare("SELECT lws_totl_rev, dte_submit, status, cust_grp FROM vqtdb1 WHERE ((dte_submit Between 1033369569 And 1054364769) And (status='10') AND (cust_grp='d' OR cust_grp='f' OR cust_grp='g' OR cust_grp='h' OR cust_grp='k' OR cust_grp='m' OR cust_grp='n' OR cust_grp='p' OR cust_grp='q' or cust_grp='s')) GROUP BY cust_grp ORDER BY cust_grp, dte_submit DESC")


or die "Couldn't prepare statement: " . $dbh->errstr;

$sth1->execute() # Execute the query
or die "Couldn't execute statement: " . $sth1->errstr;

while ($data = $sth1->fetchrow_hashref)

##########################

If more of the script is needed please let me know.

Thanks
Reply With Quote
  #2  
Old July 1st, 2003, 12:34 PM
rod k rod k is offline
Apprentice Deity
Dev Shed Loyal (3000 - 3499 posts)
  
Join Date: Jul 1999
Location: Niagara Falls (On the wrong side of the gorge)
Posts: 3,237 rod k User rank is Private First Class (20 - 50 Reputation Level)rod k User rank is Private First Class (20 - 50 Reputation Level) 
Time spent in forums: 4 m 8 sec
Reputation Power: 13
Send a message via AIM to rod k
you aren't using any aggregrate functions so group by is not appropriate.
Reply With Quote
  #3  
Old July 1st, 2003, 12:43 PM
ecwaddel99 ecwaddel99 is offline
Junior Member
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Jun 2003
Posts: 7 ecwaddel99 User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
Base on the way I have setup the above script, what options if any do I have to modify this to allow me to do the GROUP BY..... or do I need to start over?
Reply With Quote
  #4  
Old July 1st, 2003, 02:45 PM
rod k rod k is offline
Apprentice Deity
Dev Shed Loyal (3000 - 3499 posts)
  
Join Date: Jul 1999
Location: Niagara Falls (On the wrong side of the gorge)
Posts: 3,237 rod k User rank is Private First Class (20 - 50 Reputation Level)rod k User rank is Private First Class (20 - 50 Reputation Level) 
Time spent in forums: 4 m 8 sec
Reputation Power: 13
Send a message via AIM to rod k
Why do you want to use a group by? Unless you have an aggregate function(s) in your query a group by won't do anything. What do you THINK a group by is going to do for you with this query?
Reply With Quote
  #5  
Old July 1st, 2003, 02:47 PM
Onslaught's Avatar
Onslaught Onslaught is offline
/(bb|[^b]{2})/
Dev Shed Demi-God (4500 - 4999 posts)
  
Join Date: Nov 2001
Location: Somewhere in the great unknown
Posts: 4,834 Onslaught User rank is Second Lieutenant (5000 - 10000 Reputation Level)Onslaught User rank is Second Lieutenant (5000 - 10000 Reputation Level)Onslaught User rank is Second Lieutenant (5000 - 10000 Reputation Level)Onslaught User rank is Second Lieutenant (5000 - 10000 Reputation Level)Onslaught User rank is Second Lieutenant (5000 - 10000 Reputation Level)Onslaught User rank is Second Lieutenant (5000 - 10000 Reputation Level)Onslaught User rank is Second Lieutenant (5000 - 10000 Reputation Level) 
Time spent in forums: 1 Day 23 h 30 m 30 sec
Reputation Power: 88
Send a message via ICQ to Onslaught
you need some form of an aggregrate function in your sql statement. i.e. sum(), etc...
Reply With Quote
  #6  
Old July 2nd, 2003, 07:56 AM
ecwaddel99 ecwaddel99 is offline
Junior Member
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Jun 2003
Posts: 7 ecwaddel99 User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
"What do you THINK a group by is going to do for you with this query?"

Rod K
This is a cgi script that takes customers and lists them by name, date and then breaks out the $ spent. My goal is to have all the cust. $ under just their one name, not the same co name listed 20 times for 30 diff. cust., with the $ summ as one number and one cust. Below is the whole thing.
###################################3
# crev - convert revenue
sub crev {
my $ccrev;
my ($intcrev)=@_;
if ($intcrev < 10000) {$ccrev=sprintf("%d",$intcrev)};
if ($intcrev > 99999999) {$ccrev=sprintf("%3.0fm",$intcrev/1000000)};
if (($intcrev > 9999)&&($intcrev < 100000)) {$ccrev=sprintf("%3.1fk",$intcrev/1000)};
if (($intcrev > 99999)&&($intcrev < 1000000)) {$ccrev=sprintf("%3.0fk",$intcrev/1000)};
if (($intcrev > 999999)&&($intcrev < 10000000)) {$ccrev=sprintf("%3.2fm",$intcrev/1000000)};
if (($intcrev > 9999999)&&($intcrev < 100000000)) {$ccrev=sprintf("%3.1fm",$intcrev/1000000)};
return ($ccrev);
};

############################################


printf("Content-type: text/html\n\n\n<HTML><HEAD></HEAD>\n");

printf("<table border=1 cellspacing=1 style=border-width:3; border-style:ridge; bordercolorlight=#000080 bordercolordark=#000080><tr><P> </P>\n");
#printf("<td colspan=11 align=center><B><P>lws Input for 6th Level Monthly Reports<br></P>\n", $today, $Vuser);
#printf("<B><P> %s, Requested by %s</P></B><BR></td>\n", $today, $Vuser);
printf("<tr><th bgcolor=navy><font color=white>CT Group</font></th>\n");
#printf("<th bgcolor=navy><font color=white>End Customer</font></th>\n");
printf("<th bgcolor=navy><font color=white>Date Submitted</font></th>\n");
#printf("<th bgcolor=navy><font color=white>Quote Number</font></th>\n");
printf("<th bgcolor=navy><font color=white>less\$10K</font></th>\n");
printf("<th bgcolor=navy><font color=white>bet\$10K and \$100K</font></th>\n");
printf("<th bgcolor=navy><font color=white>bet\$100K and \$1M</font></th>\n");
printf("<th bgcolor=navy><font color=white>bet\$1M and \$10M</font></th>\n");
printf("<th bgcolor=navy><font color=white>\$10M+ </font></th>\n");
printf("<th bgcolor=navy><font color=white>LWS Rev</font></th></tr>\n");
#printf("<th bgcolor=navy><font color=white>seq_num</font></th></tr>\n");



# Read the matching records and print them out

$dbh1 = DBI->connect('DBI:Pg:dbname=bandp')
or die "Couldn't connect to database: " . DBI->errstr;
$sth1 = $dbh1->prepare("SELECT lws_totl_rev, dte_submit, status, cust_grp FROM vqtdb1 WHERE ((dte_submit Between 1033369569 And 1054364769) And (status='10') AND (cust_grp='d' OR cust_grp='f' OR cust_grp='g' OR cust_grp='h' OR cust_grp='k' OR cust_grp='m' OR cust_grp='n' OR cust_grp='p' OR cust_grp='q' or cust_grp='s')) ORDER BY cust_grp, dte_submit DESC")


or die "Couldn't prepare statement: " . $dbh->errstr;

$sth1->execute() # Execute the query
or die "Couldn't execute statement: " . $sth1->errstr;

while ($data = $sth1->fetchrow_hashref)

########################################

Thank you both for the input! Hopefully I can now fig. this out.
Last edited by ecwaddel99 : July 2nd, 2003 at 08:06 AM.
Reply With Quote
  #7  
Old July 2nd, 2003, 01:02 PM
ecwaddel99 ecwaddel99 is offline
Junior Member
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Jun 2003
Posts: 7 ecwaddel99 User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
Unhappy
This is what I came up with and I still can not get it to work.


$sth1 = $dbh1->prepare("SELECT lws_totl_rev, dte_submit, status, cust_grp, FROM vqtdb1 WHERE ((dte_submit Between 1033369569 And 1054364769) And (status='10') AND (cust_grp='d' OR cust_grp='f' OR cust_grp='g' OR cust_grp='h' OR cust_grp='k' OR cust_grp='m' OR cust_grp='n' OR cust_grp='p' OR cust_grp='q' or cust_grp='s') SUM (CT Group, less\$10K, bet\$10K and \$100K, bet\$1M and \$10M, \$10M+, LWS Rev) GROUP BY cust_grp ORDER BY cust_grp, dte_submit DESC")


Any suggetions?
Reply With Quote
Reply

Viewing: Dev Shed Forums > Other > Beginner Programming > Unable to get GROUP BY to work

« Previous Thread | Next Thread »

Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Linear Mode Linear Mode
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump


Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 





© 2003-2008 by Developer Shed. All rights reserved. DS Cluster 3 hosted by Hostway.