Databases
StackOverflow2013
Postsdbo
POCombined grouping on two fields
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POCombined grouping on two fields
    primarykey
    data
    text
    <p>I have access to just the SELECT and WHERE clause to a database. I have no ability to do any more than fill in the blanks between SELECT and FROM and after WHERE ("SELECT _ _ _ _ _ _ FROM TABLE WHERE _ _ _ _ _")</p> <p>In the database, there are four fields (more in fact, but these are the pertinent ones): "TShirt1", "TShirt1Size", "TShirt2" and "TShirt2Size".</p> <p>TShirt1 and TShirt2 both contain one of "RedCrew", "BlueSlim" or "GreyV"</p> <p>TShirt1Size and TShirt2Size both contain one of "S", "M", "L" or "XL"</p> <p>I want to know if I can create a query that will return the total numbers of each combination. <em>If I didn't have two fields</em>, it would be</p> <pre><code>SELECT TShirt, TShirtSize, count(*) FROM BookingsTable WHERE 1 GROUP BY TShirt, TShirtSize </code></pre> <p>so I would get results like</p> <pre><code>RedCrew, L, 2 BlueSlim, M, 4 </code></pre> <p>etc. </p> <p>Is there any way I can get the same, or similar output, but combining the results of TShirt1 and TShirt2?</p> <p>EDIT: Apologies for the confusion. The actual table would be something like (typed not tested):</p> <pre><code>create table tshirt ( TShirt_id int, TShirt1 varchar(10), TShirt1Size varchar(2), TShirt2 varchar(10), TShirt2Size varchar(2) ); insert into tshirt values (1, 'RedCrew', 'XL', 'BlueSlim', 'M'); insert into tshirt values (2, 'BlueSlim', 'L', 'RedCrew', 'L'); insert into tshirt values (3, 'GreyV', 'L', 'BlueSlim', 'M'); insert into tshirt values (4, 'BlueSlim', 'M', '', ''); insert into tshirt values (5, '', '', 'GreyV', 'L'); insert into tshirt values (6, 'BlueSlim', 'S', 'BlueSlim', 'L'); </code></pre> <p>and the result set I would want (if my math is correct) would be:</p> <pre><code>BlueSlim, S, 1 BlueSlim, M, 4 BlueSlim, L, 2 GreyV, L, 2 RedCrew, L, 1 RedCrew, XL, 1 </code></pre>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USBen Holness
    1. USBen Holness
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POCombined grouping on two fields
      1. This table or related slice is empty.
      1. VTApproveEditSuggestion
    2. VO
      singulars
      1. POCombined grouping on two fields
      1. This table or related slice is empty.
      1. VTUpMod
    1. COThere are no fields. Tables have rows and columns :-). Now consider normalizing your schema.
      singulars
      1. POCombined grouping on two fields
      1. USStrawberry
    2. COomg, i got it... using only the select statement and where clause but i'm not sure how to get the lower `group by` to work with it... i'm scared to post because i'm sure @AirThomas will downvote but i'm very proud of myself. it was hard, lol...
      singulars
      1. POCombined grouping on two fields
      1. USgloomy.penguin
    3. COOh, but does there have to be a `group by` at the end? or can it just be a `select` clause and a `from` clause only? I wrote something that boils down to just a down to a single `select` and `from` but there's subqueries in the `select`... so that might break the rules. No `where` or `group by`, though.
      singulars
      1. POCombined grouping on two fields
      1. USgloomy.penguin
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload