Jump to content

Search the Community

Showing results for tags 'GROUP BY'.

  • Search By Tags

    Type tags separated by commas.
  • Search By Author

Content Type


Forums

  • W3Schools
    • General
    • Suggestions
    • Critiques
  • HTML Forums
    • HTML/XHTML
    • CSS
  • Browser Scripting
    • JavaScript
    • VBScript
  • Server Scripting
    • Web Servers
    • Version Control
    • SQL
    • ASP
    • PHP
    • .NET
    • ColdFusion
    • Java/JSP/J2EE
    • CGI
  • XML Forums
    • XML
    • XSLT/XSL-FO
    • Schema
    • Web Services
  • Multimedia
    • Multimedia
    • FLASH

Find results in...

Find results that contain...


Date Created

  • Start

    End


Last Updated

  • Start

    End


Filter by number of...

Joined

  • Start

    End


Group


AIM


MSN


Website URL


ICQ


Yahoo


Jabber


Skype


Location


Interests


Languages

Found 2 results

  1. hi, sorry, i have a question to do...but i know how to write in sql query ....however the answer need to write on relational algebra in query is like : SELECT * FROM operation where r='broken' group by pc having count(pc) >1 ; π id,pc,result ( σ r = 'broken' (operation)) like group by and having clasue...i no idea ... i searched on internet still @@ .... isnt the answer like that, i using split/step by step method to do rel 1 = σ r = 'broken' (operation) rel 2 = σ count(pc)>1 γ pc,count(pc) (rel 1)) rel 3 = π id,pc,result (rel 2 ) thanks
  2. hi there, the following query was developed and works as it should. SELECT ( SELECT COUNT(DISTINCT SP1.bigint_ProspectID, PLS1.smallint_ProspectOrdinal) FROM 10_serviceprospects SP1 LEFT JOIN 13_prospectleadsent PLS1 ON SP1.bigint_ProspectID = PLS1.bigint_ProspectID WHERE PLS1.timestamp_Sent BETWEEN "2013-08-01 00:00:00" AND "2013-08-30 23:59:59" AND SP1.bigint_ServiceID = SS.bigint_ServiceID AND PLS1.bigint_SupplierID = S.bigint_SupplierID ) AS `Previous Month`, ( SELECT COUNT(DISTINCTROW SP2.bigint_ProspectID, PLS2.smallint_ProspectOrdinal) FROM 10_serviceprospects SP2 LEFT JOIN 13_prospectleadsent PLS2 ON SP2.bigint_ProspectID = PLS2.bigint_ProspectID WHERE PLS2.timestamp_Sent BETWEEN "2013-09-01 00:00:00" AND "2013-09-31 23:59:59" AND SP2.bigint_ServiceID = SS.bigint_ServiceID AND PLS2.bigint_SupplierID = S.bigint_SupplierID ) AS `Last Month`, ( SELECT COUNT(DISTINCT SP3.bigint_ProspectID, PLS3.smallint_ProspectOrdinal, PLS3.bigint_SupplierID) FROM 10_serviceprospects SP3 LEFT JOIN 13_prospectleadsent PLS3 ON SP3.bigint_ProspectID = PLS3.bigint_ProspectID WHERE PLS3.timestamp_Sent BETWEEN "2013-10-01 00:00:00" AND "2013-10-30 23:59:59" AND SP3.bigint_ServiceID = SS.bigint_ServiceID AND PLS3.bigint_SupplierID = S.bigint_SupplierID ) AS `Current Month`, ( SELECT COUNT(DISTINCT SP0.bigint_ProspectID, PLS0.smallint_ProspectOrdinal) FROM 10_serviceprospects SP0 LEFT JOIN 13_prospectleadsent PLS0 ON SP0.bigint_ProspectID = PLS0.bigint_ProspectID WHERE PLS0.timestamp_Sent BETWEEN "2013-08-01 00:00:00" AND "2013-10-30 23:59:59" AND SP0.bigint_ServiceID = SS.bigint_ServiceID AND PLS0.bigint_SupplierID = S.bigint_SupplierID ) AS `Total`, S.text_SupplierName AS `Supplier Name` FROM 5_suppliers S JOIN 4_servicesuppliers SS ON (S.bigint_SupplierID = SS.bigint_SupplierID) WHERE SS.bigint_ServiceID = 1 # Bakkie Canopies OnlyAND S.smallint_SupplierStatus = 4 # Freemium Suppliers OnlyGROUP BY S.bigint_SupplierID ORDER BY `Total` DESC, `Supplier Name` ASC; # Order by Total, then Supplier Name took about a day to develop this inbetween other work. it returns the correct data. the output is available at https://performatix.co/csv/Bakkie_Canopies_Freemium_Supplier_Opportunities_2013-10-09.csv i was then requested to change the ordering by `Total` and primarily by `Region Description`; as well as grouping by Region ID and primarily by Supplier ID, creating a secondary output. SELECT R.text_RegionDescription AS `Region Description`, ( SELECT COUNT(DISTINCT SP1.bigint_ProspectID, PLS1.smallint_ProspectOrdinal) FROM 10_serviceprospects SP1 LEFT JOIN 13_prospectleadsent PLS1 ON SP1.bigint_ProspectID = PLS1.bigint_ProspectID WHERE PLS1.timestamp_Sent BETWEEN "2013-08-01 00:00:00" AND "2013-08-30 23:59:59" AND SP1.bigint_ServiceID = SS.bigint_ServiceID AND PLS1.bigint_SupplierID = S.bigint_SupplierID AND SP1.bigint_RegionID = R.bigint_RegionID ) AS `Previous Month`, ( SELECT COUNT(DISTINCTROW SP2.bigint_ProspectID, PLS2.smallint_ProspectOrdinal) FROM 10_serviceprospects SP2 LEFT JOIN 13_prospectleadsent PLS2 ON SP2.bigint_ProspectID = PLS2.bigint_ProspectID WHERE PLS2.timestamp_Sent BETWEEN "2013-09-01 00:00:00" AND "2013-09-31 23:59:59" AND SP2.bigint_ServiceID = SS.bigint_ServiceID AND PLS2.bigint_SupplierID = S.bigint_SupplierID AND SP2.bigint_RegionID = R.bigint_RegionID ) AS `Last Month`, ( SELECT COUNT(DISTINCT SP3.bigint_ProspectID, PLS3.smallint_ProspectOrdinal, PLS3.bigint_SupplierID) FROM 10_serviceprospects SP3 LEFT JOIN 13_prospectleadsent PLS3 ON SP3.bigint_ProspectID = PLS3.bigint_ProspectID WHERE PLS3.timestamp_Sent BETWEEN "2013-10-01 00:00:00" AND "2013-10-30 23:59:59" AND SP3.bigint_ServiceID = SS.bigint_ServiceID AND PLS3.bigint_SupplierID = S.bigint_SupplierID AND SP3.bigint_RegionID = R.bigint_RegionID ) AS `Current Month`, ( SELECT COUNT(DISTINCT SP0.bigint_ProspectID, PLS0.smallint_ProspectOrdinal) FROM 10_serviceprospects SP0 LEFT JOIN 13_prospectleadsent PLS0 ON SP0.bigint_ProspectID = PLS0.bigint_ProspectID WHERE PLS0.timestamp_Sent BETWEEN "2013-08-01 00:00:00" AND "2013-10-30 23:59:59" AND SP0.bigint_ServiceID = SS.bigint_ServiceID AND PLS0.bigint_SupplierID = S.bigint_SupplierID AND SP0.bigint_RegionID = R.bigint_RegionID ) AS `Total`, S.text_SupplierName AS `Supplier Name` FROM 5_suppliers S JOIN 4_servicesuppliers SS ON (S.bigint_SupplierID = SS.bigint_SupplierID) JOIN 1_regions R ON (SS.bigint_RegionID = R.bigint_RegionID)WHERE SS.bigint_ServiceID = 1 # Bakkie Canopies OnlyAND S.smallint_SupplierStatus = 4 # Freemium Suppliers OnlyGROUP BY SS.bigint_RegionID, SS.bigint_SupplierID ORDER BY `Total` DESC, `Region Description` ASC; # Order by Total, then Region Description this took about an hour inbetween other work. the results are in https://performatix.co/csv/Bakkie_Canopies_Regional_Freemium_Supplier_Opportunities_2013-10-09.csv however you will notice that the values do not corroborate with the first (this was discovered recently after reviewing the csv's! what is wrong with this query to get it to corroborate with the first?). i was then asked to order by `Total` but primarily by `Region Description`, and group by Region ID - creating a third output. SELECT R.text_RegionDescription AS `Region Description`, ( SELECT COUNT(DISTINCT SP1.bigint_ProspectID, PLS1.smallint_ProspectOrdinal) FROM 10_serviceprospects SP1 LEFT JOIN 13_prospectleadsent PLS1 ON SP1.bigint_ProspectID = PLS1.bigint_ProspectID WHERE PLS1.timestamp_Sent BETWEEN "2013-08-01 00:00:00" AND "2013-08-30 23:59:59" AND SP1.bigint_ServiceID = SS.bigint_ServiceID AND PLS1.bigint_SupplierID = S.bigint_SupplierID AND SP1.bigint_RegionID = R.bigint_RegionID ) AS `Previous Month`, ( SELECT COUNT(DISTINCTROW SP2.bigint_ProspectID, PLS2.smallint_ProspectOrdinal) FROM 10_serviceprospects SP2 LEFT JOIN 13_prospectleadsent PLS2 ON SP2.bigint_ProspectID = PLS2.bigint_ProspectID WHERE PLS2.timestamp_Sent BETWEEN "2013-09-01 00:00:00" AND "2013-09-31 23:59:59" AND SP2.bigint_ServiceID = SS.bigint_ServiceID AND PLS2.bigint_SupplierID = S.bigint_SupplierID AND SP2.bigint_RegionID = R.bigint_RegionID ) AS `Last Month`, ( SELECT COUNT(DISTINCT SP3.bigint_ProspectID, PLS3.smallint_ProspectOrdinal, PLS3.bigint_SupplierID) FROM 10_serviceprospects SP3 LEFT JOIN 13_prospectleadsent PLS3 ON SP3.bigint_ProspectID = PLS3.bigint_ProspectID WHERE PLS3.timestamp_Sent BETWEEN "2013-10-01 00:00:00" AND "2013-10-30 23:59:59" AND SP3.bigint_ServiceID = SS.bigint_ServiceID AND PLS3.bigint_SupplierID = S.bigint_SupplierID AND SP3.bigint_RegionID = R.bigint_RegionID ) AS `Current Month`, ( SELECT COUNT(DISTINCT SP0.bigint_ProspectID, PLS0.smallint_ProspectOrdinal) FROM 10_serviceprospects SP0 LEFT JOIN 13_prospectleadsent PLS0 ON SP0.bigint_ProspectID = PLS0.bigint_ProspectID WHERE PLS0.timestamp_Sent BETWEEN "2013-08-01 00:00:00" AND "2013-10-30 23:59:59" AND SP0.bigint_ServiceID = SS.bigint_ServiceID AND PLS0.bigint_SupplierID = S.bigint_SupplierID AND SP0.bigint_RegionID = R.bigint_RegionID ) AS `Total`FROM 5_suppliers S JOIN 4_servicesuppliers SS ON (S.bigint_SupplierID = SS.bigint_SupplierID) JOIN 1_regions R ON (SS.bigint_RegionID = R.bigint_RegionID)WHERE SS.bigint_ServiceID = 1 # Bakkie Canopies OnlyAND S.smallint_SupplierStatus = 4 # Freemium Suppliers OnlyGROUP BY SS.bigint_RegionID ORDER BY `Total` DESC, `Region Description` ASC; # Order by Total, then Region Description this took approximately another hour between other work. the results are in https://performatix.co/csv/Bakkie_Canopies_Regional_Freemium_Supplier_Opportunities_2013-10-09.csv once again - the data does not corroborate with the first csv, the issue i am facing is that i do not understand why the queries are not synchronous to the data... the first query works just as well for a different service, however the second and third queries do not return anything at all. i am assuming that this is because they do not have varying regions (all the same). further than this i just keep hitting my head... anyone comfortable enough to have a look at the queries and suggest changes other than the ones i applied? the data from the tables are available at https://performatix.co/csv/1_regions, 2_servicescatalogue, 4_servicesuppliers, 5_suppliers, 10_serviceprospects & 13_prospectleadsent.csv.
×
×
  • Create New...