Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2009
    Posts
    168

    Unanswered: Grouping columns from sql select statement

    I am retrieving some data from Invoices, Customers and Companies tables as follows, but would like to make the customerName and the Companies.Name as single column such Name and similarly for customerID/companyID and customerCode/companyCode. Any suggestions please? Thanks.

    Code:
    with cte
    as
    (		
    
    	select		distinct i.invoiceNumber, itemID, customers.customerID, Companies.companyID
    				,SUM(net_weight) as totalWeight, rate
    				,(select SUM(net_weight) * rate)  as amount
    	from		Invoices i inner join InvoicesDetails on i.invoiceID = InvoicesDetails.invoiceID
    	LEFT JOIN	Companies ON i.companyID = Companies.companyID 
    	LEFT JOIN	Customers ON i.customerID = Customers.customerID 
    	where		DATENAME(month,i.sell_date) = 'January' 
    	and			YEAR(i.sell_date) = '2014' 
    	group by	invoiceNumber, rate, itemID ,customers.customerID,Companies.companyID,rate
    )
    
    select		customers.firstname + ' ' + customers.surname as customerName
    			,customers.code as customerCode
    			,cte.invoiceNumber,cte.itemID, cte.totalWeight, cte.rate, cte.amount
    			, companies.name 
    			, cte.companyID, cte.customerID,
    			Companies.code as companyCode
    from		Companies 
    RIGHT JOIN	cte ON cte.companyID = Companies.companyID 
    LEFT JOIN	Customers ON cte.customerID = Customers.customerID

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'm confused... It appears to me that you've already combined the data into single columns.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Jul 2009
    Posts
    168
    Hi Pat,

    Sorry for the confusion but I havent:

    select customers.firstname + ' ' + customers.surname as customerName ===> CustomerName column
    companies.name ====> CompanyName

    Please have a look at what am getting at the moment:
    Would like the name and customerName to be 1 column.

    Click image for larger version. 

Name:	sql.JPG 
Views:	4 
Size:	246.2 KB 
ID:	16120

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    See if the kpeeroo column does what you want.
    Code:
    with cte as (		
    select distinct i.invoiceNumber, itemID
    ,  customers.customerID, Companies.companyID
    ,  SUM(net_weight) as totalWeight, rate
    ,  SUM(net_weight) * rate as amount
       FROM Invoices i
       INNER JOINT InvoicesDetails
          ON i.invoiceID = InvoicesDetails.invoiceID
    	 LEFT JOIN	Companies
    	    ON i.companyID = Companies.companyID 
    	 LEFT JOIN	Customers
    	    ON i.customerID = Customers.customerID 
    	 WHERE  DATENAME(month,i.sell_date) = 'January' 
    	    AND YEAR(i.sell_date) = '2014' 
    	 GROUP BY invoiceNumber, rate, itemID, customers.customerID, Companies.companyID, rate
    )
    SELECT Coalesce(customers.firstname, '')
    +  Coalesce(' ' + customers.surname, '')
    +  Coalesce(' ' + Companies.Name, '') AS kpeeroo
    ,  customers.code as customerCode
    ,  cte.invoiceNumber,cte.itemID, cte.totalWeight, cte.rate, cte.amount
    ,  companies.name 
    ,  cte.companyID, cte.customerID
    ,  Companies.code as companyCode
       FROM Companies 
       RIGHT JOIN	cte
          ON cte.companyID = Companies.companyID 
       LEFT JOIN	Customers
          ON cte.customerID = Customers.customerID
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Jul 2009
    Posts
    168
    Brilliant! Did the same for CustomerCode, CompanyCode. Works perfectly as wanted.
    Thanks for your precious help Pat.

  6. #6
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1
    WHERE is the DDL?

    I am going to guess that you have no idea what ISO-11179 or other data modeling standards are. For example, “rate”, “code”, “amount”, “name”, et al are too generic to be a valid column name. I will guess this is a “shipping rate”, etc, but who knows? Could be interest, flow, or anything.

    The ISO_11179 rules call these things an attribute property and they have to be part of a data element name of the form “<attribute>_<attribute property>” to be valid. They are commonly abbreviated to 2 or 3 letters. Note the underscore; camelCase does not work and there are also some Unicode considerations in ISO.

    Why do you think we can guess the correct tables for the columns? Use table aliases consistently and think about the guy who has to maintain this code.

    Why did you invent your own “item_id” instead of using an industry standard like EAN, UPC, GTIN, etc?

    Why did you invent your own “company_id” instead of using the industry standard DUNS?

    Why do you treat dates as strings? SQL has temporal data types. This is COBOL programming. This mindset error is also why you put commas at the start of a line (this comes from punch cards in the 1950's) You also do more display formatting in the query! That is pure 1950's programming; in modern tiered architectures, all display is done in presentation layers.

    Multiple outer joins are possible but they are a bad code smell that says you have orphans and/or no DRI to references between weak and strong entities. Even worse, a SELECT DISTINCT is how you tell the world you made a CROSS JOIN error and have to repair it at great expense on the fly at run time. I assume the invoice_nbr is a key to invoices table, so how can it be duplicated? Usually the weak entities have attributes that belong in the strong entities or vice versa. Too bad we have no DDL

    Putting subqueries in a SELECT list is a performance disaster. Why did you use a generic “CTE” and not a VIEW? These guys are virtual tables and should have clear precise names just like a base table.
    The idiom in SQL is a grouping on the strong entity.

    CREATE VIEW Invoice_Summaries
    AS
    (SELECT I.invoice_nbr, I.customer_id, I.gtin, I.duns, I.shipping_rate,
    SUM(ID.net_weight) AS net_wgt_tot,
    SUM(ID.net_weight) * I.shipping_rate AS shipping_amt,
    WHERE I.invoice_nbr = ID.invoice_nbr
    GROUP BY I.invoice_nbr, I.customer_id I.gtin, I.duns, I.shipping_rate);

    This is a guess, but it looks like a handy table (view) to have and to share among users. This is another COBOL mindset error. Back then, we wrote a single program for each problem. Over adns over, or pulled the text out of a copy book. In SQL, we write re-usable modules like VIEWs and PROCEDUREs for everyone.

    Where are your calendar tables? This is another basic idiom of SQL.

    Since SQL is a database language, we prefer to do look ups and not calculations and certainly not COBOL string functions for display. Joins can be optimized while temporal math and strings messes up optimization. A useful idiom is a report period calendar that everyone uses so there is no way to get disagreements in the DML.

    The report period table gives a name to a range of dates that is common to the entire enterprise. Talk to accounting about what you use.

    CREATE TABLE Monthly_Periods
    (month_name CHAR(10) NOT NULL PRIMARY KEY
    CHECK (month_name LIKE '[12][0-9][0-9][0-9]-[01][0-9]-00'),
    month_start_date DATE NOT NULL,
    month_end_date DATE NOT NULL,
    CONSTRAINT date_ordering
    CHECK (month_start_date <= month_end_date),
    etc);

    These report periods can overlap or have gaps. I like the MySQL convention of using double zeroes for months and years, That is 'yyyy-mm-00' for a month within a year and 'yyyy-00-00' for the whole year. The advantages are that it will sort with the ISO-8601 data format required by Standard SQL and it is language independent. The pattern for validation is '[12][0-9][0-9][0-9]-00-00' and '[12][0-9][0-9][0-9]-[01][0-9]-00'

    I will guess you do not need the outer joins now. Here is possible query to pass to the report layer:

    SELECT CU.first_name, CU.last_name, CU.customer_code,
    M.month_name, S.invoice_nbr, S.gtin,
    S.net_wgt_tot, S.shipping_rate,
    S.shipping_amt, C.company_name, S.duns,
    FROM Companies AS C,
    Customers AS CU,
    Invoice_Summaries AS S,
    Monthly_Periods AS M
    WHERE S.customer_id = CU.customer_id
    AND S.duns = C.duns
    AND M.month_start_date AND M.month_end_date
    -- AND M.month_name = '2014-01-00'
    ;

    Now, look at the ROLLUP option and see what it might be used to do for you.

  7. #7
    Join Date
    Jul 2009
    Posts
    168
    Listen bro whoever you are, good you showed your intelligence rather than helping someone out. You complicate things for people rather than ease things up boasting up your supposed knowledge.

    I know what you are saying there thanks to intelligent people like Michael Hernandez who put things clear enough for everyone unlike people like you.

    I assume the invoice_nbr is a key to invoices table,
    Stop assuming, there is a primary key and all the tables are normalized.

    This mindset error is also why you put commas at the start of a line (this comes from punch cards in the 1950's) You also do more display formatting in the query! That is pure 1950's programming; in modern tiered architectures, all display is done in presentation layers.
    The date in the table is SQL Datetime and is converted.

    Mate, this is just for simplifying the presentation here but if people like you dont understand then am please Pat understood. And what you blabbering about, this is in a STORED PROCEDURE and am using WPF for presentation.




    Quote Originally Posted by Celko View Post
    WHERE is the DDL?

    I am going to guess that you have no idea what ISO-11179 or other data modeling standards are. For example, “rate”, “code”, “amount”, “name”, et al are too generic to be a valid column name. I will guess this is a “shipping rate”, etc, but who knows? Could be interest, flow, or anything.

    The ISO_11179 rules call these things an attribute property and they have to be part of a data element name of the form “<attribute>_<attribute property>” to be valid. They are commonly abbreviated to 2 or 3 letters. Note the underscore; camelCase does not work and there are also some Unicode considerations in ISO.

    Why do you think we can guess the correct tables for the columns? Use table aliases consistently and think about the guy who has to maintain this code.

    Why did you invent your own “item_id” instead of using an industry standard like EAN, UPC, GTIN, etc?

    Why did you invent your own “company_id” instead of using the industry standard DUNS?

    Why do you treat dates as strings? SQL has temporal data types. This is COBOL programming. This mindset error is also why you put commas at the start of a line (this comes from punch cards in the 1950's) You also do more display formatting in the query! That is pure 1950's programming; in modern tiered architectures, all display is done in presentation layers.

    Multiple outer joins are possible but they are a bad code smell that says you have orphans and/or no DRI to references between weak and strong entities. Even worse, a SELECT DISTINCT is how you tell the world you made a CROSS JOIN error and have to repair it at great expense on the fly at run time. I assume the invoice_nbr is a key to invoices table, so how can it be duplicated? Usually the weak entities have attributes that belong in the strong entities or vice versa. Too bad we have no DDL

    Putting subqueries in a SELECT list is a performance disaster. Why did you use a generic “CTE” and not a VIEW? These guys are virtual tables and should have clear precise names just like a base table.
    The idiom in SQL is a grouping on the strong entity.

    CREATE VIEW Invoice_Summaries
    AS
    (SELECT I.invoice_nbr, I.customer_id, I.gtin, I.duns, I.shipping_rate,
    SUM(ID.net_weight) AS net_wgt_tot,
    SUM(ID.net_weight) * I.shipping_rate AS shipping_amt,
    WHERE I.invoice_nbr = ID.invoice_nbr
    GROUP BY I.invoice_nbr, I.customer_id I.gtin, I.duns, I.shipping_rate);

    This is a guess, but it looks like a handy table (view) to have and to share among users. This is another COBOL mindset error. Back then, we wrote a single program for each problem. Over adns over, or pulled the text out of a copy book. In SQL, we write re-usable modules like VIEWs and PROCEDUREs for everyone.

    Where are your calendar tables? This is another basic idiom of SQL.

    Since SQL is a database language, we prefer to do look ups and not calculations and certainly not COBOL string functions for display. Joins can be optimized while temporal math and strings messes up optimization. A useful idiom is a report period calendar that everyone uses so there is no way to get disagreements in the DML.

    The report period table gives a name to a range of dates that is common to the entire enterprise. Talk to accounting about what you use.

    CREATE TABLE Monthly_Periods
    (month_name CHAR(10) NOT NULL PRIMARY KEY
    CHECK (month_name LIKE '[12][0-9][0-9][0-9]-[01][0-9]-00'),
    month_start_date DATE NOT NULL,
    month_end_date DATE NOT NULL,
    CONSTRAINT date_ordering
    CHECK (month_start_date <= month_end_date),
    etc);

    These report periods can overlap or have gaps. I like the MySQL convention of using double zeroes for months and years, That is 'yyyy-mm-00' for a month within a year and 'yyyy-00-00' for the whole year. The advantages are that it will sort with the ISO-8601 data format required by Standard SQL and it is language independent. The pattern for validation is '[12][0-9][0-9][0-9]-00-00' and '[12][0-9][0-9][0-9]-[01][0-9]-00'

    I will guess you do not need the outer joins now. Here is possible query to pass to the report layer:

    SELECT CU.first_name, CU.last_name, CU.customer_code,
    M.month_name, S.invoice_nbr, S.gtin,
    S.net_wgt_tot, S.shipping_rate,
    S.shipping_amt, C.company_name, S.duns,
    FROM Companies AS C,
    Customers AS CU,
    Invoice_Summaries AS S,
    Monthly_Periods AS M
    WHERE S.customer_id = CU.customer_id
    AND S.duns = C.duns
    AND M.month_start_date AND M.month_end_date
    -- AND M.month_name = '2014-01-00'
    ;

    Now, look at the ROLLUP option and see what it might be used to do for you.
    Last edited by kpeeroo; 01-26-15 at 07:40.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •